Using DATEDIFF in Excel 2010 VBA

Attached is a workbook, with a worksheet that simulates the formatting and information that we use.  The format of the current report needs to stay intact, although the number of rows change daily.

The question is what would be the best practice for using DATEDIFF in VBA, under the "Differential" Headings.
DatedIff-Needed.xlsm
Cook09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cook09Author Commented:
I have come up with some  workable code for DatedDiff, the issue now would be the best looping method to place the values into the adjacent cell.

Sub TestDateFunction()
Dim wks As Worksheet, pDate1 As Date, pDate2 As Date
Dim wksPostedLate As Worksheet
  Set wksPostedLate = Worksheets("Posted Late")
      pDate1 = Range("G9")
      pDate2 = Range("F9")
      wksPostedLate.Activate
  MsgBox DateDiff("n", pDate1, pDate2)   'n = minutes
End Sub

Open in new window

0
gowflowCommented:
Here it is.

Check the attached workbook and make sure your macros are activated and press on the button Update Differential and check the results.

Note you have mistakenly put the title to be 'Differnetial' where it should be 'Differential'  I build the check around that string so if you choose to change it to 'Differential' make sure you modifiy the macro on this line

If WS.Cells(I, "F") <> "" And WS.Cells(I, "G") <> "" And WS.Cells(I, "H") <> "Differnetial" Then

Open in new window


to be
If WS.Cells(I, "F") <> "" And WS.Cells(I, "G") <> "" And WS.Cells(I, "H") <> "Differential" Then

Open in new window


You noted that it is not necessary to use the function DateDiff as you may endup in situations of hours that complicate the issue. Excel is smarter than this simply substracting and getting the correct format to the cell will do the job.

Enjoy
gowflow
DatedIff-Needed.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FaustulusCommented:
I wonder if a simple worksheet function would work for you.
In cell H9 enter
=F9-G9

Open in new window

Format the cell as Time, either "hh:mm:ss" or "[mm]:ss".
The second format will convert hours to minutes, so that you will see 73:14 (in H18) instead of 1:13:14.
Copy the formula along with the format down your list as required.

BTW, a differential is the construction of gears in a motor car's axle that makes the inner wheel (when driving in a circle) turn slower than the outer wheel. What you are calculaing is, simply, a difference.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

gowflowCommented:
Faustulus
You maybe right and it would have been my first attempt to answer this question after reading it the first time, however reading it again I noticed that not only the user wanted a VBA solution but the sentence

quote
 although the number of rows change daily.
unquote

lead me to believe that either this workbook was updated daily by a certain macro that would dump info in there that could make the titles and rows differ or an other macro was creating from scratch this sheet

these two situations obviously would require a vba solution and not a formula as more adequate and less manipulation.

For sure will wait for Cook09 to advise.
gowflow
0
Cook09Author Commented:
Yes, this is fluid, and VBA may be the only viable solution.  To save some time, I've written some code to get to where I need to insert a XXX.formula= "...."  

After a column is produced, then each section within the column needs a heading.  The code is below.
           
  If Cells(i, j).text = "" Then 
               Cells(i, j).Select
                 With Selection
                  .Interior.Color = 12485200 'RGB(80, 130, 190)
                  .FormulaR1C1 = "Hours"
                  .Font.Color = RGB(255, 255, 255)
                  .Font.Size = 11
                  .Font.Bold = True
                  .Rows(i).HorizontalAlignment = xlCenter
                 End With

Open in new window

After the "End With," then the formula code needs inserted, below the "Hours" Heading. BTW, Differential Calculus is the study of the rates at which quantities change.  

I've been trying to use Cells(i, j).Offset, but not having any luck so far in putting a formula below the heading that subtacts the smaller from the larger, or to use DatedDiff.
Any thoughts on how to proceed from here?
0
FaustulusCommented:
Please try the code in the attached worksheet. I didn't quite know how to integrate your own code, last posted, but perhaps the way I did makes sense.
Note that, in accordance with your latest instruction, the "Differential" is now the absolute difference between the two dates, regardless of which one was earlier or later.
EXX-131019-Date-Difference.xlsm
0
gowflowCommented:
Cook9
Did you try the file I posted in ID: 39579159
gowflow
0
Cook09Author Commented:
Yes, you are correct about the definition. The differential is "set in stone" for each item.  How the differential is used within the logic of the application, could be variable.  

I haven't opened the file yet, just dropped by on a Saturday to check on a couple of items.  Part of what I was "toying" with prior to leaving yesterday was determining the addresses of the two date values, and then, as suggested, putting those addresses into a formula and copying down.

While I didn't get that far prior to leaving, what I did notice was the address of the cell is expressed in absolute terms versus variable ones, i.e. $G$8$ - $F$8.  If one does a copy and paste down in VBA, will they still be static or will the formula adjust?  It would seem they would stay static.  If so, how does one make them variable, so they can be copied down?

I did just open the file and VBA, and have some questions that I'll want to discuss on Monday.
   For instance, in the code:
       
For R = NwsFirstDataRow To LastRow(NwsBatch, Ws)

Open in new window

Why is the Ws used as it is?  Since there are several sheets involved, each sheet will have to be looped through. Is this a way to also do this?

To start, each worksheet is formatted seperately, as there are some anomalies with each one. Then for a common operation, like the one below, an array is created and then looped.
For Each wks In Worksheets(Array("Posted Late", "Misdelivered", "Late & Missing", "Error Items")
      Wks.Activate
      lLastRow = LastDataRow  'a seperate function
       Do something that would be common to the worksheets
  Next wks

Open in new window


For example:
Sub Delete_dba_Text()
Dim wks As Worksheet, Counter As Double, strText As String, lastrow As Long
    For Each wks In Worksheets(Array("Posted Late", "Misdelivered", "Late & Missing", "Error Items"))
        lLastRow = LastDataRow
        For i = 2 To lLastRow
            If Cells(i, 1).Value Like "The Package database*" Then
            Rows(i).EntireRow.Delete
            End If
        Next i
    Next wks
End Sub

Open in new window


We'll discuss more on Monday..Cheers,
Cook
0
gowflowCommented:
I fail to understand why you do not reply to my question ?
It is the second time I ask you did you try the file I posted ???
gowflow
0
FaustulusCommented:
@gowflow
I think Cook did refer to your workbook. He is copying code in Saturday's response that isn't lifted from my suggestion.

@cook09
Yes - more or less - that is the way it is done.
    Set Ws = ThisWorkbook.Sheets("Posted Late")
This is the line in my code (please don't confuse or mix it with gowflow's code which takes a different approach) determines what "Ws" will henceforth stand for. It would be standard to create a little loop in which the name "Posted late" would be changed and all the remaining code would then be executed on that other worksheet.
Meanwhile, using VBA to write formulas is a little like asking a mechanic to find a mechanic to fix the car. Code can write the result of a formula in less time than it takes to write the formula. My code is doing that. At the same time it also formats the cell and adds a column header. Clearly: VBA 1, Formula 0.

Now, applying the same code to other worksheets which are slightly different requires a lot of extra information which you haven't provided. Rightly so, I think, because that would be another question which can be opened when the first one is resolved. Trying to move the goal posts now will only delay progress.
0
gowflowCommented:
Faustulus
Do not see where he is pulling info from my code !!!!

this ...
For R = NwsFirstDataRow To LastRow(NwsBatch, Ws)

Open in new window


is your code

this ...
For Each wks In Worksheets(Array("Posted Late", "Misdelivered", "Late & Missing", "Error Items")
      Wks.Activate
      lLastRow = LastDataRow  'a seperate function
       Do something that would be common to the worksheets
  Next wks 

Open in new window

is his own code

and
Sub Delete_dba_Text()
...
is also his own code.

So simply my posts here has been totally overlooked and not bothered to answer. I will wait for Cook9 clarification.

gowflow
0
FaustulusCommented:
@gowflow
I see. Give him some time. It's Sunday, and it seems its only the two of us looking for action here. Lol:
The Persians have a saying: when business is slow the barbers trim each other's beards.
0
gowflowCommented:
hahah !!! no problem for me. But always kinda frustrating when ur totally ignored and disreaguarded !!

Hv a nice sunday, and a good shave !!! :)
gowflow
0
gowflowCommented:
@Cook9
Did you have a chance to look at the solution that I posted ? it is in ID: 39579159 first thread of this question.

gowflow
0
Cook09Author Commented:
I'm going to close this thread as the primary solution did come goflow.  Although, I did appreciate what Faustulus provided.  The problem with part of his solution, was that I couldn't understand the code in detail to really use it as a primary source.
Maybe I wasn't looking in the right place, but I couldn't find how "NwsBatch" obtained its value, as the Sub was just starting.  It's obviously very sophisticated and I would like to know how these pieces of code worked.  Plus, I took his advice not to use formulas.

Most of the time, from the last post, has been putting all of the code together from what I found in the code provided.  For some reason, even though goflow posted his workbook, I missed it, and just saw the code within the Comment Box. In looking down the thread, there was the workbook that faustulus uploaded and opened that one. Fortunately, with the prodding of goflow, I saw his upload and most of it is what was neeeded.  Obviously, with a few changes.  

The initial formatting of the workbook is, for the most part, complete.  Now, comes some of the logic needed for this project.

To also address the scope creep issue, or "moving the goal posts," yes, it was headed in that direction, so that's why I'm accepting this topic, and will post the next issue, which should be easy (dates), but just can't seem to get it to subtract.  Since you're familiar with what I'm doing, I'd appreciate your feed back.

Thanks for your help,
Cook
0
Cook09Author Commented:
Sorry, my grammer was not checked, it should have been, "did come from goflow."

Thanks again, I'll now post another.
0
FaustulusCommented:
An enum is just a list of names and values. It is the quickest way to assign values to variables.
Values need not necessarily be assigned. If no value is assigned the first item in the list will have a value of zero. All other enumerations take the value of the preceding one + 1. Therefore a list of three enumerations, none of them with an assigned value, will have the values 0, 1 and 2.
If a value is assigned to any enumeration the next item in the list will take that value + 1.
Enums can have negative values, but only integers.
VB and Excel use enums extensively for their own purposes. variables like xlLeft, xlRight, xlUp or xlDown, vbYes, vbNo, vbCancel and many hundreds more are all enums.
Note, if you wish me to be sure to look at your new question please post a link here so that I will be notified.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.