The following function will do this. If a value of True is passed into it as
the optional blnShowDays argument it shows the days, if not the total hours,
so:
TimeDuration(StartDateTime, EndDateTime,True)
might return:
2 days 2:57:07
while
TimeDuration(StartDateTime, EndDateTime)
would return:
50:57:07
It would be trivial task to amend it to return the format dd:hh:nn:ss.
BTW the date format is irrelevant. Date/time values in Access are stored
as a 64 bit floating point number with the integer part representing the days
and the fractional part the times of day, with an origin at 30 December 1899
00:00:00; the format is merely how you choose to see them. Here's the
function:
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
Ken Sheridan
Stafford, England