• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

Subtracting Dates in Excel VBA

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
Cook09
Asked:
Cook09
  • 3
  • 2
2 Solutions
 
byundtCommented:
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
 
gowflowCommented:
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
 
Cook09Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:
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
 
Cook09Author Commented:
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
 
Cook09Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now