Problems totaling dates (1 Viewer)

[mastermind]

I am trying to run a query that subtracts the stop time/date to the start time/date. It returns the correct results except for when the difference is greater than 28 hours. If it is greater than 28 hours it resets itself to 0 and starts counting again. Is there anyway that I can get it to tell me how hours and minutes without reseting itself.

 

[ColinEssex]

Hi
Have you sorted this yet? Is the problem that the time goes over midnight?

Col

 

[mastermind]

The date is rolling over for times that go over midnight. Do you think that the datediff function will correct this issue? If so how would I go about doing this. Assuming that the field names are start_time and stop_time. Thanks,

 

[ColinEssex]

Hi

Copy this into the modules

Function FormatInterval(ByVal Interval As Variant) As String
Dim days As Long, Hours As Long, Minutes As Long, Seconds As Long
If IsNull(Interval) Then Exit Function
'
' Calculate hours
Interval = Interval * 24


Hours = Int(Interval)
Interval = Interval - Hours
' Calculate minutes
Interval = Interval * 60
Minutes = Int(Interval)
Interval = Interval - Minutes
' Calculate seconds
Seconds = Int(Interval * 60 + 0.5)

If Seconds = 60 Then
Minutes = Minutes + 1
Seconds = 0
End If

If Minutes >= 60 Then
Hours = Hours + 1
Minutes = Minutes - 60
End If

If Hours < 0 Then
Hours = Hours + 24
End If


FormatInterval = Hours & ":" & Format(Minutes, "00")
End Function

Now in a query create a calculated field like this-
Timediff:FormatInterval([Stop_Time]-[Start_Time])
now run it

Col

 

[mastermind]

Thanks, it worked! Now how do I go about totaling these numbers?