Introduction.
How do we calculate the difference in time 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 difference between them. For instance, subtracting the start time of 12:30:45 from the end time of 13:30:15 gives a result of 00:59:30. However, if the end time occurs past midnight, such as 01:30:15, how should we calculate the time difference?
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 increments 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 at 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 the date 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
This is the kind of value (Current Date and Time) returned with the function Now(). You can format this value as date alone (dd-mm-yyyy) or 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 result value we get in days doesn’t matter if the date is the same or a different date. If the date is the same, you will get the result in days, like 0.9999999. All we have to do is convert these days 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 in 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.