Subtracting Dates in Excel VBA

Posted on 2013-10-24
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.

Question by:Cook09
  • 3
  • 2
LVL 81

Assisted Solution

byundt earned 100 total points
ID: 39599223
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
End With

Open in new window

LVL 29

Accepted Solution

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
                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
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

Author Comment

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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 29

Expert Comment

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

Author Comment

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.


Author Closing Comment

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.


Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel User Form VBA Help 18 30
MS excel check if Links exist 5 35
Remove duplicates using cell values VBA 2 28
Copy a range from 1..n excel sheets to one destination sheet 2 30
Many companies are making the switch from Microsoft to Google Apps ( Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

786 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