How to calculate elapsed time between two given times?

[Sun_Force]

I'm trying to calculate elapsed time between StartTime and EndTime in seconds.

Both StartTime & EndTime are bound text boxes (datetime data type) in a form.
The table contains another field (RegisteredOn) which has a default value of =Date().

My first attempt was using DateDiff. But the result is not correct in some situations like this:
StartTime : 08:00 PM
EndTime : 01:30 AM

?DateDiff("s", .StartTime , .EndTime )
-69300

If I include date with the recorded time, DateDiff may work, but it's not desirable for me to type something like 2021/09/10 08:00 PM each time.
If there's a way to let me type only minutes and seconds but keep the date together to ease the calculation, it'll be OK too.

This elapsed time will be used for calculating operating ratio for a manufacturing line.
Any kind of advice is much appreciated.[/code]

 

[theDBguy]

Maybe use an IIf() expression to add 24 hours to end time if it's lesser than start time.

 

[Sun_Force]

Maybe use an IIf() expression to add 24 hours to end time if it's lesser than start time.

At present I'm doing this:

If .EndTime> .StartTime Then
            ProcessedTime = DateDiff("s", .StartTime , .EndTime)
        Else
            ProcessedTime = DateDiff("s", .StartTime , "23:59") + 60 + DateDiff("s", "00:00", .EndTime)
        End If

But your solution seems to be more ellegant.
Let me try and see how it'll be.

Thank you.

 

[Pat Hartman]

You can default to the current date if that is what you are asking. Just concatenate it in the CONTROL's AfterUpdate event.

 

[theDBguy]

At present I'm doing this:

If .EndTime> .StartTime Then
            ProcessedTime = DateDiff("s", .StartTime , .EndTime)
        Else
            ProcessedTime = DateDiff("s", .StartTime , "23:59") + 60 + DateDiff("s", "00:00", .EndTime)
        End If

But your solution seems to be more ellegant.
Let me try and see how it'll be.

Thank you.

Hi. Not sure if you had a chance to try it yet, but I was thinking more along this line.

ProcessedTime: DateDiff("s",[StartTime],IIf([StartTime]>[EndTime],1,0)+[EndTime])

Hope that helps...

 

[Sun_Force]

Hi. Not sure if you had a chance to try it yet, but I was thinking more along this line.

ProcessedTime: DateDiff("s",[StartTime],IIf([StartTime]>[EndTime],1,0)+[EndTime])

Hope that helps...

Works like a charm.
Thanks.

 

[theDBguy]

Works like a charm.
Thanks.

Hi. Glad we could assist. Good luck with your project.