That's because "h" is the interval character for hours. The DateDiff
function returns whole intervals, so zero is being returned. The interval
character for minutes is "n".
If your values are being entered as time values only then the difference will
compute correctly provided that the start and end times do not span midnight,
otherwise you'll get an incorrect result. This is because there is no such
thing in Access as a time value per se, only a date/time value. When you
enter a time you are in fact entering the time on 30 December 1899, which is
day-zero in Access's date/time implementation.
If you need to cope with times spanning midnight which are entered as times
without a date element then the following function will return the difference
in hours as a decimal number:
Function ElapsedHours(startTime As Date, endTime As Date) As Double
Dim intMinutes As Integer, intHours As Integer
' adjust start time if later than end time
' by subtracting one day
If startTime > endTime Then
startTime = DateAdd("d", -1, startTime)
End If
' get difference in minutes
intMinutes = DateDiff("n", startTime, endTime)
' get difference in complete hours
intHours = intMinutes \ 60
ElapsedHours = intHours + ((intMinutes / 60) - intHours)
End Function
The return value can be converted to a string in hh:nn format with the
following function:
Public Function HoursFormatted(dblHours As Double) As String
Dim strHours As String, strMinutes As String
' get whole hours
strHours = Int(dblHours)
' get and format minutes
strMinutes = Format((dblHours - Int(dblHours)) * 60, "00")
HoursFormatted = strHours & ":" & strMinutes
End Function
so HoursFormatted(ElapsedHours([Start Time],[End Time]))
would return a value such as 1:30
Ken Sheridan
Stafford, England
Thanks for your help!
I have 2 military time fields and am wishing to get the difference between
them and to have the result in minutes.
TotalTime: DateDiff("H",[Start Time],[End Time])
The result of this SQL does not result in exact minutes, as it returns 0
when the answer is really 30.
I'd like to see it return hr:min or even all minutes.
13:00-11:30 results in 1:30 (as in 1hr and 30mins or 90min)
