Calculate time difference when using time only fields Most of the time I used datetime fields, but for this project it is a requirement to use time fields. The problem is calculations with 2 times not on the same date.

I.E.:

23:00 to 03:00 is 4 hours.

In vba I'm using the following code:

Me.TotalTime = IIf(Round(CCur(Nz(DateDiff("n", [startTime], [endTime]) / 60, 0)), 2) &lt; 0, Round(CCur(Nz(DateDiff("n", [startTime], 1) / 60, 0)), 2) + Round(CCur(Nz(DateDiff("n", 0, [endTime]) / 60, 0)), 2), Round(CCur(Nz(DateDiff("n", [startTime], [endTime]) / 60, 0)), 2))


Is this the correct way to calculate and or is there an easier method?
You don't seem to require worrying about time zones or daylight savings. So if you just want a simple calculation to determine the duration between two times, then a simple function will work:

Function DurationDiff(startTime As Double, endTime As Double) As Double
    '--- assumes startTime is the start time in decimal hours (0.0 to 23.99)
    '            endTime   is the ending time in decimal hours
    '    returns duration between the times in decimal hours
    '        (accounts for time crossing midnight)
    If endTime &lt; startTime Then
        '--- midnight crossing!
        endTime = endTime + 24
    End If
    DurationDiff = endTime - startTime
End Function


Running a quick test with this function gives you:

Sub DurationTest()
    Debug.Print DurationDiff(startTime:=9#, endTime:=17#) &amp; " hours"
    Debug.Print DurationDiff(startTime:=23#, endTime:=3#) &amp; " hours"
    Debug.Print DurationDiff(startTime:=11#, endTime:=15#) &amp; " hours"
    Debug.Print DurationDiff(startTime:=15#, endTime:=3#) &amp; " hours"
End Sub


With the output:


8 hours
4 hours
4 hours
12 hours



I think it then reduces your calculation to nothing more than

Me.TotalTime = Nz(DurationDiff([startTime], [endTime]))