Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Subtracting Dates in Excel VBA

Posted on 2013-10-24
6
Medium Priority
?
348 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 400 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 31

Accepted Solution

by:
gowflow earned 1600 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 31

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

670 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