Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Tracking Operation Time: Advanced DateDiff

Scripting for the Enterprise

Tracking Operation Time: Advanced DateDiff

by  markdmac  Posted    (Edited  )
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! :-(

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: 
[green]
'==========================================================================
'
' 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.
'
'==========================================================================
[/green]
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 = ""
        [green]'Start with total number of days[/green]
        days = DateDiff("d",d1,d2)
  		[green]'Convert days to years and grab remaining days[/green]
		If days > 365 Then
			years = days\365
			days = days Mod (365*years)-1
			report = years & " Year(s), "
		Else 
			years = 0	
		End If
		[green]'Thank you PHV for help simplifying the month calculation
		'Compute the number of months[/green]
		months = Int(DateDiff("m",d1,d2))+(day(d2)<day(d1))
		[green]'remove years from the total months[/green]
		months = months Mod 12
		report = report & Months & " Month(s), "
		
		[green]'now find the days[/green]
		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	
        [green]
        '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[/green]
        Seconds = abs(datediff("S", d1, d2)) 
        if Seconds <= 0 then 
            report = "0 seconds." 
        else 
	        Seconds = Seconds mod (24*60*60) 
	        [green]'divide by 3600 to get hours[/green]
	        If Seconds >= 3600 then 
	            report = report & _  
	            Seconds\(3600) & " hours(s), " 
	        end If
	        [green]'use mod to get remaining seconds and divide to get minutes[/green]
	        Seconds = Seconds mod (60*60) 
	        if Seconds >= 60 then 
	            report = report & _  
	            Seconds\(60) & " minutes(s), " 
	        end If
	        [green]'use mod to get remaining seconds[/green]
	        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.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top