×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBScript FAQ

Scripting for the Enterprise

Tracking Operation Time: Advanced DateDiff by markdmac
Posted: 9 Jun 07 (Edited 13 Jun 12)

markdmac's Enterprise Ready Scripts

By Mark D. MacLachlan, The Spiders Parlor
http://www.thespidersparlor.com


While writing a script for a customer recently I needed to record the start time and end time for a backup operation and report the total time to complete the operation.

Using DateDiff one would think it is easy to report the time between two time stamps. The theory is figure out the total number of seconds between the time stamps and then start dividing that number to figure out the number of days, hours, minutes and seconds.

This was all rather straight forward and plenty of examples exist for this. But I wanted to take things a step further. If the number of days was something like 62 I wanted a report that told me 2 months and 2 days. But then I started to really think about that. 62 days is equal to 2 months and 2 days if I assume each month only has 30 days. Then I thought about February and leap years! sad

Good grief!

Further testing and exploration of DateDiff revealed some other "anomolies". Consider the following simple code.

CODE

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 don't know about you, but I don't consider that an exceptable answer.

My first priority was to figure out how many days there were between dates. Using that information I could easily determine the number of years by dividing the days by 365. Using MOD I can get the remainder of that same operation to determine the number of days left over.

A quick example to illustrate this:

CODE

sTime = "6/30/2002 4:15:00 PM"
eTime = "9/30/2007 5:25:50 PM"

WScript.Echo datediffToWords(sTime, eTime)

Function datediffToWords(d1, d2)
report = ""
days = DateDiff("d",d1,d2)

If days > 365 Then
years = days\365
days = days Mod (365*years)-1

Else
years = 0
End If
report = years & " Year(s), " & days & " Day(s)"
datediffToWords = report
End Function

Note that the days gets subtracted by 1. This is because of the way DateDiff figures out the days, it is always 1 too high.

So in the above example we get output of 5 Year(s), 92 Day(s).

I have an accurate number of years, but I needed a way to break down the Days into Months and Days. We can't just divide the number of days because the number of days in a month fluctuates between months.

So, then I started to think. I only wanted to know how many full months there are. Using DateDiff's month did not report the number of whole months, and depending on the number of actual days between dates seemed to skew my results.

My solution was to figure out when the first day of the next month was. Working with that as my start date I could determine the number of full months.

Tweaking the original example:

CODE

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)
fullmonthStart = Month(d1) + 1 & "/1/" & Year(d1)
months = Int(DateDiff("m",fullmonthStart,d2))
WScript.Echo months & " months"
End Function

Running the above now accurately reports the number of months.

While working on this, Tek-Tips MVP PHV suggested an alternative one line solution to get the proper number of months.

CODE

months = Int(DateDiff("m",d1,d2))+(day(d2)<day(d1))

Brilliant! This works perectly and saves me a few lines of code. It took me while to get it, so I send thanks to PHV for helping my over tired brain to follow the logic. In the above example, the logic breaks down to the following.

months = Int(DateDiff("m",d1,d2))+(day(d2)<day(d1))
months = (1) + ( 1 < 29)
months = 1 + (1<29)
1<29 evaluates to true (true in boolean is -1) Therefore...
months = 1 + (-1)
months = 0

To get the number of days will depend on the full months between the days. It is necessary to figure out the number of days till the end of the month from the start date and add to that the number of days in the end date but only if the day in the start date is higher than the day in the end date. If the day in the end date is higher than the day in the start date then we just subtract them.

Does your head hurt like mine?

As an example:

Start: 1/30/2007
End: 3/2/2007

There is one full month between these dates. (February) There is one day between the start date and the end of the month. There are two days in the end date. So we get 1 month and 3 days.

Another example:

Start: 3/8/2007
End: 4/9/2007

In the above example, there is 1 month between the dates. The day in the start date is less than the day in the end date, so we subtract them to get the number of days. Our result is 1 month and 1 day.


OK, so at this point I now have the proper number of years, months and days. Next on tap is determining the right number of hours, minutes and seconds.

Things get easier here because we know how many seconds there are in a minute. Multiply 60 seconds by 60 minutes to get one hour. Multiply the hours by 24 for days.

So to start figuring out our time we first need to strip out the days. We do that using the mod operator. Mod returns the remainder of division.

Let's look at an easy example:

Start: "5/8/2007 4:15:00 AM"
End: "5/9/2007 5:16:05 AM"

We can clearly see that we have 1 day, 1 hour, 1 minute and 5 seconds between these dates. So let's break it down.

DateDiff("s",d1,d2) with the above dates will yield 90065 seconds total.

We want to remove the day from that so we want the remainder of dividing 90065 by the number of seconds in a day.

90065 mod (24*60*60) = 3665

We now have a total of 3665 seconds to convert to hours, minutes and seconds since we stripped out the days.

60 seconds * 60 minutes = 3600

So there are 3600 seconds in an hour.

If we divide 3665 by 3600 we get 1, or one hour. Using the vbscript Mod operator again we can get the remainder.

3665 Mod 3600 = 65

So we know we have 1 hour and 65 seconds.

65 seconds is greater than a minute so we divide that by 60 to get our minutes. We repeat the Mod to get the remaining seconds.

65/60 = 1 minute
65 Mod 60 = 5 seconds

Therefore 3665 seconds is equal to 1 hour, 1 minute and 5 seconds.

So get to the final code already right! OK here we go. Below is a function that you can use. You need only call it by providing two dates and it will report back to you the time between the dates. Since it is a function, you should not need to alter the code at all. Just copy/paste the function into your script and call it as demonstrated.

CODE


'==========================================================================
'
' NAME: DateDiffToWords.vbs
'
' AUTHOR: Mark D. MacLachlan , The Spider's Parlor
' URL: http://www.thespidersparlor.com
' DATE : 6/9/2007
' COPYRIGHT (c) 2007 All Rights Reserved
'
' COMMENT: Reports Years, months, days, hours, minutes, seconds between dates
'
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF
' ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED To
' THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
' PARTICULAR PURPOSE.
'
' IN NO EVENT SHALL THE SPIDER'S PARLOR AND/OR ITS RESPECTIVE SUPPLIERS
' BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY
' DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
' WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS
' ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE
' OF THIS CODE OR INFORMATION.
'
'==========================================================================

sTime = "6/30/2002 4:15:00 PM"
eTime = "9/30/2007 5:25:50 PM"

WScript.Echo datediffToWords(sTime, eTime)

Function datediffToWords(d1, d2)
report = ""
'Start with total number of days
days = DateDiff("d",d1,d2)
'Convert days to years and grab remaining days
If days > 365 Then
years = days\365
days = days Mod (365*years)-1
report = years & " Year(s), "
Else
years = 0
End If
'Thank you PHV for help simplifying the month calculation
'Compute the number of months

months = Int(DateDiff("m",d1,d2))+(day(d2)<day(d1))
'remove years from the total months
months = months Mod 12
report = report & Months & " Month(s), "

'now find the days
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

If Day(d1) =< Day(d2) Then
days = Day(d2) - Day(d1)
Else
days = DateDiff("d", newStart, fullmonthStart) + Day(d2) -1
End If
If days > 0 Then
report = report & days & " day(s), "
End If

'now we will deal with the time left over
'begin by getting total seconds between dates and divide out the days
'grab the remaining seconds with the mod operator

Seconds = abs(datediff("S", d1, d2))
if Seconds <= 0 then
report = "0 seconds."
else
Seconds = Seconds mod (24*60*60)
'divide by 3600 to get hours
If Seconds >= 3600 then
report = report & _
Seconds\(3600) & " hours(s), "
end If
'use mod to get remaining seconds and divide to get minutes
Seconds = Seconds mod (60*60)
if Seconds >= 60 then
report = report & _
Seconds\(60) & " minutes(s), "
end If
'use mod to get remaining seconds
seconds = Seconds Mod (60)
report = report & seconds & " second(s)"
end if
datediffToWords = report
End Function

I wish to thank all who vote on this FAQ. I strive for nothing less than 10s. Before you vote, if you don't think this FAQ rates a 10 please provide feedback to me first. Also please check out my other FAQs in this same forum.
Happy scripting.

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.

Back to VBScript FAQ Index
Back to VBScript Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close