Solved

Subtracting Dates in Excel VBA

Posted on 2013-10-24
6
336 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 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 30

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 30

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

691 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