Calculate Start and End Time Stored as HH:MM when Shift extend past 00:00 (midnight)?

[onmyway]

Hi all,

I hope you can help me.

I have a simple calculation error due to, what I believe, is a format issue.

Detail:

i have two fields used for capturing a start and end time - txtWorkShiftStart and txtWorkShiftEnd. The format of these two fields are HH:MM. To calculate the total shift time, one would subtract the two values (End - Start).

Here is the code i use for the calculation:

Please Login or Register  to view this content.

This seems to work fine, until you have a shift starting before 00:00 (midnight) and end after 00:00 (midnight).

Example:

04:00 - 20:00 = 16:00 WRONG REMEMBER: it is a shift that started at 20:00 and ended 04:00 the next morning

It should be :

04:00 - 20:00 = 08:00 CORRECT REMEMBER: it is a shift that started at 20:00 and ended 04:00 the next morning

i thank you in advance!

[Bernie Deitrick]

ws.Cells(rowToEdit, 138).Value = Format(CDate(Me.txtWorkShiftEnd.Value) - CDate(Me.txtWorkShiftStart.Value) + IIf(CDate(Me.txtWorkShiftEnd.Value) < CDate(Me.txtWorkShiftStart.Value), 1, 0), "HH:MM")

should work, as long as they are time strings and don't include the date.

[LJMetzger]

Hi onmyway,

Try adding one day, when the end time occurs before the start time.

Please Login or Register  to view this content.

Lewis

[onmyway]

ws.Cells(rowToEdit, 138).Value = Format(CDate(Me.txtWorkShiftEnd.Value) - CDate(Me.txtWorkShiftStart.Value) + IIf(CDate(Me.txtWorkShiftEnd.Value) < CDate(Me.txtWorkShiftStart.Value), 1, 0), "HH:MM")

should work, as long as they are time strings and don't include the date.

Hi Bernie,

Thank you, works like a charm!

[onmyway]

Hi onmyway,

Try adding one day, when the end time occurs before the start time.

Please Login or Register  to view this content.

Lewis

Hi Lewis,

Thanks for helping me once again. You really know your stuff!

In essence, both your solution and Bernie's make use of the same methodology. I decided to go with Bernie's, as it was simpler.