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.


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) < 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?

  • 1
    \$\begingroup\$ Will this help? \$\endgroup\$ – PeterT Apr 13 '17 at 16:18
  • \$\begingroup\$ You need a strategy to deal with changes in daylight saving time, or where the the source of the data records in local time and moves from one time zone to another. Since you don't know the date, the problem will have to be solved by the user or device entering the data. \$\endgroup\$ – Gerard Ashton Apr 14 '17 at 11:06
  • 3
    \$\begingroup\$ Where's the rest of the code? Any context? When you say time only, you mean disregard any date? \$\endgroup\$ – Raystafarian Apr 20 '17 at 19:37
  • 3
    \$\begingroup\$ Have you heard of line breaks or helper variables or functions? You're doing the same calculation four times! \$\endgroup\$ – t3chb0t Apr 28 '17 at 17:45
  • \$\begingroup\$ @PeterT, couldn't get your solution to work. \$\endgroup\$ – davejal Apr 28 '17 at 21:35

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 < 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#) & " hours"
    Debug.Print DurationDiff(startTime:=23#, endTime:=3#) & " hours"
    Debug.Print DurationDiff(startTime:=11#, endTime:=15#) & " hours"
    Debug.Print DurationDiff(startTime:=15#, endTime:=3#) & " 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]))
| improve this answer | |
  • \$\begingroup\$ I will test it in the office by tuesday, but it seems a solid solution. +1 \$\endgroup\$ – davejal Apr 29 '17 at 17:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.