Advanced DateDiff 1

[markdmac]

I am working on a function to report the total Years, Months, Days, Hours, Minutes, Seconds between two date stamps.

I have most of it working but in my testing realized something. The following gives incorrect output:

sTime = "7/29/2007 4:15:00 AM"
eTime = "8/1/2007 6:29:50 PM"

WScript.Echo datediffToWords(sTime, eTime)

Function datediffToWords(d1, d2) 
  months = Int(DateDiff("m",d1,d2))
  WScript.Echo months & " months"
End Function

Despite only being a day and a half different, DateDiff reports there is a whole month between the dates. I am assuming that DateDiff is rounding up from a decimal. Anyone know how I can get anything less than a full month to report as zero months?

I considered using the number of days total between them, but converting days to months & days using MOD is not accurate either since the number of days in a month varies.

Any help appreciated.

 

[PHV]

A starting point:
months = Int(DateDiff("m",d1,d2))+(day(d2)<day(d1))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[markdmac]

Thanks for your response PHV. I am afraid I am not following the logic.

If number of months + number of days in end date < number of days in start address?

Were I to use 2/28/2007 as a start date and 3/29/2007 as an end date I don't see how that would accurately report the months.

The above is obviously just a snippet of code to illustrate my problem. Here is a little more to work with.

sTime = "1/30/2006 4:15:00 AM"
eTime = "8/1/2007 6:29:50 PM"

WScript.Echo datediffToWords(sTime, eTime)

Function datediffToWords(d1, d2) 
  days = DateDiff("d",d1,d2)
  
  If days > 365 Then
  	years = Int(days/365)
  	days = days Mod 365
  Else 
  	years = 0	
  End If
  WScript.Echo "Years:" & years & " Days:" & days
End Function

So the above will yield 1 Year and 183 days. I need to convert the 183 days to an accurate number of months and remaining days.

While typing the above an idea came to me. here is my solution that seems to work. My concern was to get an accurate number of months, so I need to adjust my start date to the beginning of the next month and work from there. I also had to account for if the days in start date was greater or less than days in the end date since that will mark a full month. For the days left I then just need to figure out the number of days between my start date and the beginning of the next month, plus the number of days in my end date. As long as I account for December start dates which would start a fresh month in the new year I belive I have my answer. Leap years should also not affect this computation.

Here is my solution which seems to be working like a champ. I tested with many date combinations. Let me know if you see any errors in my logic please. Many thanks.

sTime = "12/12/2006 4:15:00 AM"
eTime = "1/15/2007 6:29:50 PM"

WScript.Echo datediffToWords(sTime, eTime)

Function datediffToWords(d1, d2) 
  days = DateDiff("d",d1,d2)
  
  If days > 365 Then
  	years = Int(days/365)
  	days = days Mod (365*years)-1
  Else 
  	years = 0	
  End If
  
  newStart = Month(d1) & "/" & Day(d1) & "/" & Year(d1) + years
  If Month(d1) <> 12 Then
  	fullmonthStart = Month(d1) + 1 & "/1/" & Year(d1) + years
  Else
  	fullmonthStart = "1/1/" & Year(d1) + years +1
  End If
  fullmonthEnd = Month(d2) & "/1/" & Year(d2)
  months = DateDiff("m", fullmonthStart,fullmonthEnd)
  If Day(d1) < Day(d2) Then
  	Months = Months +1
  End If
  If months < 0 Then
  	months = 0
  End If
  If Day(d1) < Day(d2) Then
  	days = Day(d2) - Day(d1)
  Else
    days = DateDiff("d", newStart, fullmonthStart) + Day(d2) -1
  End If
  
  WScript.Echo "Years:" & years & " Months:" & months & " Days:" & days
End Function

I'll post the more robust version that will also report hours, minutes & seconds as an FAQ.



I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[PHV]

I am afraid I am not following the logic
A boolean = -1 when true, so you subtract one from the result is the end day is less than than the start day.
Did you ever TRY my suggestion ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[markdmac]

I confess I did but I did not understand it, hence my follow up question.

I can see that it works though I confess I am still struggling with understanding it. What is throwing me off is the <.

sTime = "7/29/2007 4:15:00 AM"
eTime = "8/1/2007 6:29:50 PM"

WScript.Echo datediffToWords(sTime, eTime)

Function datediffToWords(d1, d2)
months = Int(DateDiff("m",d1,d2))+(day(d2)[b]<[/b]day(d1)) 
[red] 'months  =             1        +    1      <   29[/red]
 dateDiffToWords = months
End Function

So I see that the above become months=2<29 which should be evaluated as a boolean true and therefore be equal to -1.

What I don't get is how it is returning the actual number especially when I change the dates to be further apart.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[PHV]

months = 1 + (1<29)
1<29 evaluates to true,so;
months = 1 + (-1)

Clearer now ?

 

[markdmac]

Aha!

Yes! Thanks for your patience with me PHV. I have been looking at my screen for way too long in the past 24 hours.

 

[markdmac]

FYI: I have created faq329-6661 and included your suggestion in my solution (along with proper credit)

Probably take a day or so for the FAQ to be visible.

Thanks again PHV.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.