Solved

Subtracting Dates in Excel VBA

Posted on 2013-10-24
6
294 Views
Last Modified: 2013-10-28
I'm attaching a workbook that has a module (Format_Calc_Sheet) used for the initial formatting of the data.  The Formatting code does work, for this first step. The code within the workbook, just provides a frame of reference of how the "look and feel" of the worksheet was generated.  It initially is downloaded from a third party, and not VBA friendly,

Toward the end of the code is where I would like some assistance. As can be seen, Column H: just displays Hours and Minutes, but there is more within the cell that just "[h]:mm" .

However, what is needed, is the correct VBA syntax, to subtract the "[h]:mm" in column H:, from a fixed value (30 minutes), and if the value in the column is greater than 30 minutes, then that row is highlighted.

Cook
Error-Log-Sandbox.xlsm
0
Comment
Question by:Cook09
  • 3
  • 2
6 Comments
 
LVL 80

Assisted Solution

by:byundt
byundt earned 100 total points
ID: 39599223
Cook,
The following snippet will highlight those cells where column H is a number that exceeds 30 minutes.
With wks
    For i = 9 To lrow
        If IsNumeric(.Cells(i, "H")) Then
            If .Cells(i, "H") > TimeSerial(0, 30, 0) Then
                Range(.Cells(i, 1), .Cells(i, 9)).Interior.ColorIndex = 6
            End If
        End If
    Next
End With

Open in new window

Brad
0
 
LVL 29

Accepted Solution

by:
gowflow earned 400 total points
ID: 39599839
Although byundt's solution color the required row, upon running your macro I noticed that it is eating couple of columns which for it I commented out this line

'Range("B:C,G:G,K:K,N:N").Delete shift:=xlToLeft

and also commented out this part from your code that you attempted to color the row that is more than 30 seconds as it was failing too to to first line and last line
dTime = dTime.NumberFormat = "[h]:mm"
dTime being a variable cannot be assigned a property.

'If wks.Cells(i, vCol1).Value <= DateAdd("n", -30, Cells(i, vCol1).Value) Then Range("A" & i & ":", "I" & i).Interior.Color = wdColorGray25
             ' Debug.Print DateAdd("n", -30, Cells(i, vCol1).Value)
             ' dTime = wks.Cells(i, vCol1)
             'dTime = DateAdd("n", -30, dTime)
             ' TimeDiff = Minute(wks.Cells(i, vCol1).Value)
              'dTime = dTime.NumberFormat = "[h]:mm"

I incorporated this block

'---> Check if Time > 30min the color Row from A to I in Yellow
            '     OR else then remove formating color and set it to nil color
            If wks.Cells(i, vCol1) >= TimeSerial(0, 30, 0) Then
                wks.Range("A" & i & ":I" & i).Interior.ColorIndex = 6
            Else
                wks.Range("A" & i & ":I" & i).Interior.ColorIndex = 0
            End If

Open in new window


Further below and what it does is the following:
Color in Yellow if time is > or equal to 30 minutes
and
Remove Color otherwhyse

Last but not least
I AM ATTACHING THE FULL FILE please try the file (lets not re-do the same mistake of previous question) which I thank you to noting at the end.

Just load the file and activate your macro PostedLate
Regards
gowflow
Error-Log-Sandbox.xlsm
0
 

Author Comment

by:Cook09
ID: 39601834
Well I'm in a quandry.  The code when used for just "Posted Late" (gowflow's code) seems to work. When I bring it up to the second Sub, that handles the others, the timeserial doesn't work, as timeserial and the interior coloring of a row or not.  As can be seen, I have changed it up a little, based upon how it looked after running it and seeing the results.  However, the overall concept didn't really change.  At least it didn't appear to change.  In trying Brad's code toward the bottom of the second Sub,"TrackingText," it would color any value within the cell.  Tried using goflow's and same thing occured.  It's probably where and how I'm using it within the Sub.

The workbook I'm attaching has "Posted Late" already formatted, except the dates and the interior color.  The other's are as they come direct from the third party program; to provide a reference of where this all has to start in reformatting the data.

Maybe you can see if anything was materially changed so that it won't respond, as it does in the "Posted Late" Sub.
Error-Log-Report-3-.xlsm.xls
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:gowflow
ID: 39602532
ok as there is no automation in your workbook what macro you activate and which one has an error ?

I see you kept the line Range("B:C,G:G,K:K,N:N").Delete shift:=xlToLeft
where I had commented it out as it was deleting columns and saw no where any code that would create them back.

and ran the macro PostedLate as you did it and removing the delete previous row and it did not color

So I noticed you had substituted my line of code
wks.Range("A" & i & ":I" & i).Interior.ColorIndex = 6

by this line
wks.Range("A" & i & ":I" & i).Interior.Color = RGB(240, 240, 240)

So I put back my line of code and it worked butifully.

Your decision
gowflow
0
 

Author Comment

by:Cook09
ID: 39606691
It was my bad on the last one, I was using different criteria and that's the reason it didn't work.   However, everything else seemed to go okay.  I don't know why the code didn't come over, but I'm posting the latest code within a Word doc so you can see what I've done.   The reason that some of the columns didn't seem to work on, “Posted Late,” is that they were already formatted to their correct position.

I will have another question or two to post, one that may deal with holidays, and copying / pasting the non-colored interior cells to a separate page, that is formatted to look like a Word Document (No Grid Lines, White interior color, headings that resemble each page, and copied to the correct city destination based upon whether it should be Origin or Destination.  But, that I'll post probably tomorrow.

As of now, it all seems to work okay...but at some point may need to be optimized.

Cook
Error-Report-Code.docx
0
 

Author Closing Comment

by:Cook09
ID: 39606697
I appreciate going through the code and commenting on areas that may seem to be of concern.  It made me go back and rerun it just to make sure.

Cook
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now