The following function will return the time difference between two date/time
values:
Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String
Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
dblDuration = dtmTo - dtmFrom
'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")
' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")
If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY
TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If
End Function
Paste the function into a standard module, and if it’s a new module save it
under a different name, e.g. mdlDateTimeStuff.
You can then call it by passing the start and end date time values into it
like so:
Difference:TimeDuration([Start], [End])
Its important to understand that there is no such thing in Access as a time
value per se, only date time values. If you enter a time value its actually
stored as the time on 30 December 1899, which is 'day zero' in Access's
implementation of the date/time data type. Consequently
TimeDuration(#19:15#,#1:05AM#) will return the negative result of -6:10:00 as
Access treats the times as being both on the same day (30 December 1899). So
if your start and end times include the actual dates as well as the times the
function will work fine, but if your values are entered as times only you'll
get incorrect results. Provided that your time differences will always be
less than 24 hours you can amend the function as follows to cater for 'time
only' values being entered:
Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String
Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
' add this new line
If dtmFrom > dtmTo Then dtmTo = DateAdd("d", 1, dtmTo)
dblDuration = dtmTo – dtmFrom
' and so on as before
This does limit the use of the function to these specific circumstances,
however.
If you want to show the number of days in the return value rather than the
total hours, then pass in True as the third optional argument:
Difference:TimeDuration([Start], [End], True)
This will return the difference in a format along these lines:
2 days 3:45:00
Ken Sheridan
Stafford, England
jor said:
Thanks clifford, am really new on this and i really appreciate ur help. I am
using the code on my query and it works. Another question is, how can i
apply this for the rest of my data which has different dates and time.
Thanks again for your help.
Clifford Bass said:
Hi Jor,
If you are crossing through midnight you need to include the dates also:
DateDiff("h", #11/11/2008 19:15#, #11/12/2008 1:05#) & ":" &
Format((DateDiff("n", #11/11/08 19:15#, #11/12/08 1:05#) Mod 60), "00")
Also the first DateDiff should use "h", not "n".
Clifford Bass
:
Is there a way to get time in a "Military Time" format from a DateDiff formula?
example:
Start End DIFFERENCE
19:15 1:05AM 5:50
I am using below formula but it gives me = -18:-10 result.
Difference: DateDiff("n", [Start], [End]) & ":" &
Format((DateDiff("n",[Start], [Out]) Mod 60), "00")
Thanks
