Calculating Time Differences.
How do we calculate the Time Difference in Hours, Minutes, and Seconds between two time periods? The time period can be within the same date or across different dates.
Example-1:
Start Time: 21-03-2012 12:30:45
End Time: 21-03-2012 13:30:15
Example-2:
Start Time: 21-03-2012 12:30:45
End Time: 24-03-2012 15:15:15
In the first example, the date portion of the Start Time and End Time is irrelevant when calculating the time difference between them. For example, subtracting the Start Time of 12:30:45 from the End Time of 13:30:15 yields a time difference of 00:59:30. However, how should the time difference be calculated if the End Time occurs after midnight, for example, at 01:30:15?
00:00:00 Time.
First, let’s consider how time resets after 23:59:59 at night and returns to 00:00:00. Understanding how this transition is stored in a computer’s memory makes it much easier to work with date and time values.
We all know the basic units: a day has 24 hours, an hour has 60 minutes, a minute has 60 seconds, and a second can be further divided into 1000 milliseconds.
If you’re curious about how February gets 29 days, why every 100th year (such as 1700, 1800, 1900, or 2100) is not a leap year, why every 400th year is a leap year, and why every 4000th year is not a leap year, [click here to find out].
One Day in Seconds.
Setting aside milliseconds for the moment, one day equals 86,400 seconds (24 × 60 × 60). Put another way, one second equals 1/86,400 or 0.0000115740740741 days. Exactly one second after midnight, the computer’s internal time value becomes 0.0000115740740741, and this value is added with every passing second.
If we divide this value by 1000, we get the fraction that represents one millisecond, meaning the time value can also be incremented in millisecond intervals. At 12:00 noon, the internal time value is 0.5 (half a day), and at 23:59:59, it reaches 0.999988425926 (in days). When the clock rolls over to 00:00:00 midnight, the value resets to 0.
Date and Time Together.
To calculate the time difference between values that span different dates, the date component must be considered along with the time value, as shown in Example 2 above. In the computer’s memory, the date is stored as a continuous serial number beginning with 1 for 31-12-1899. For instance, if you create a Date/Time field in a table with a default value of 0, the field will display as 30-12-1899 until an actual date is entered.
Date and Time Number.
The date 21-03-2012 equals the date number 40989, and the Start Time (Example-2) in memory will be 40989.521354166700; the number after the decimal point is the time value. Since the Date and Time values held in memory are real numbers, it is easy to calculate the difference between them. All you have to do is subtract one number from another. This works with Date/Time Values on the same day or on different dates.
Let us find out the time difference between the date and time values in Example 2:
StartTime = #21-03-2012 12:30:45# = 40989.521354166700
The Function Now() returns the current Date and Time values together, as shown above. You can format this value as a Date alone (dd-mm-yyyy), time alone ("hh:nn:ss"), or both combined - format(now(),”dd-mm-yyyy hh:nn:ss”).
Use ? format(StartTime,”0.000000000000”) to display the Date/Time value as a real number from the StartTime Variable in the Debug Window.
EndTime = #24-03-2012 15:15:15# = 40992.635590277800
Difference = (EndTime - StartTime) = 3.114236111112 days
So, the returned value is always expressed in days, regardless of whether the Start Time and End Time fall on the same date or different dates. If both date values are the same, the result will still be returned as a fractional number of days, such as 0.9999999. All we need to do is convert this fractional day value into Hours, Minutes, and Seconds.
Total_Seconds = Difference x 86400 (difference in days converted into total seconds) = 269070.000000077 Seconds
Hours = Int(Total_Seconds / 3600) = 74 Hrs.
Minutes = int((Total_Second MOD 3600)/60) = 44 Min.
Seconds = Total_Seconds MOD 60 = 30 sec.
If we assemble all this information into a small function, we can easily find the time difference in Hours, Minutes, and Seconds by using the Start and End Date/Time Values to the Function as parameters.
The Hours/Minutes Function.
Copy and paste the following Function Code into the Standard Module and save it:
Public Function HrsMin(ByVal startDate As Date, ByVal endDate As Date) As String Dim diff As Double Dim difHrs As Integer Dim difMin As Integer Dim difSec As Integer diff = (endDate - startDate) * 86400 difHrs = Int(diff / 3600) difMin = Int((diff Mod 3600) / 60) difSec = (diff Mod 60) HrsMin = Format(difHrs, "00") & ":" & Format(difMin, "00") & ":" & Format(difSec, "00") End Function
You may call the Function HrsMin() directly from the Debug Window, as shown below, to test the code:
? HrsMin(#21-03-2012 12:30:45#,#24-03-2012 15:15:15#)Result: 74:44:30
You may call the function from a Textbox in the Form like:
= HrsMin([StartDate],[EndDate])
Or from the Query Column like:
HMS:HrsMin([StartDate],[EndDate])
Or you may run it from VBA Code:
strHMS = HrsMin(dtSDate,dtEDate)
Earlier Post Link References:
No comments:
Post a Comment
Comments subject to moderation before publishing.