time calculation (97)

Topic

New Reply

Hi all, could someone please offer a solution to calculation time difference (in hours) between two dates? thanks, Van

Reply | Quote

Replies

Hi Van,

This function calculates the elapsed time between two values and
formats the result in four different ways.

The function accepts interval arguments such as the following:
#12/15/02 6:00:00AM# – #12/15/02 10:00:00PM#

-or-

[End Time]-[Start Time]

‘ Called by ElapsedTime(#12/15/2002 11:23:00 PM# – #12/15/2002 11:12:12 AM#)
Function ElapsedTime(interval)
Dim x
x = Int(CSng(interval * 24 * 3600)) & ” Seconds”
Debug.Print x
x = Int(CSng(interval * 24 * 60)) & “:” & Format(interval, “ss”) _
& ” Minutes:Seconds”
Debug.Print x
x = Int(CSng(interval * 24)) & “:” & Format(interval, “nn:ss”) _
& ” Hours:Minutes:Seconds”
Debug.Print x
x = Int(CSng(interval)) & ” days ” & Format(interval, “hh”) _
& ” Hours ” & Format(interval, “nn”) & ” Minutes ” & _
Format(interval, “ss”) & ” Seconds”
Debug.Print x

End Function

Best,

Bob

Reply | Quote

I have the same question – except I want my time difference to show hours and minutes – i.e., time in is 8:00 am and time out is 9:15 am I want total time to reflect 1.25 hours. this is in query and form.

thanks

Reply | Quote

Dates and times are stored in Access in the form of numbers. The integer part is the number of days since December 31, 1899 and the fractional part is the time of day (midnight is .0, 6 AM is .25, noon is .5 etcetera).

If you subtract two dates/times, you get a number that represents a number of days; to get the number of hours, multiply this by 24. You can check this by opening the Debug window Ctrl+G) and typing
? (#9:15#-#8:00#)
You’ll get 5.20833333E-2 meaning 0.052083333 – the difference is a bit more than 0.05 day.
If you type
? (#9:15#-#8:00#)*24
you’ll get 1.25

In a query, you can use
Difference: ([EndTime]-[StartTime])*24

In a form or report, you can set the Control Source of a text box to
=([EndTime]-[StartTime])*24

In both cases, you can set the Format property to display the number of decimals you want.

Reply | Quote

If all you need is the elapsed hours between two Date/Time values … then all you need is to use is the DateDiff() Function:

DateDiff(“h”, [YourStartValue], [YourEndValue])

HTH
RDH

Reply | Quote