Substract hours when inicial time is before midnight and end time is after

[Lberteh]

Hi all,

I've been trying to come up with a solution for this but didn't succeed so far.

I need to calculate the effective time of each activity in my company, but as we have 24 hours shift, some activities start at, for example, 23:00 and finish at 00:30. I get 22:30 as a result instead of 01:30. Here is the code.


Private Sub TextBox2_Change()
TextBox3 = Format(TimeValue(TextBox1) - TimeValue(TextBox2), "hh:nn")

If I try to change the value to 24:30 instead of 00:30 I get an error msg.

Any ideas?

[Doug Robbins]

If the start time was in cell A1 and the end time was in cell B1 then


Dim lngStart As Long, lngEnd As Long
lngStart = Format(Sheets(1).Range("A1"), "hh") * 60 + Format(Sheets(1).Range("A1"), "nn")
lngEnd = Format(Sheets(1).Range("B1"), "hh") * 60 + Format(Sheets(1).Range("B1"), "nn")
If Format(Sheets(1).Range("A1"), "hh") > Format(Sheets(1).Range("B1"), "hh") Then
lngEnd = lngEnd + 24 * 60
End If
Duration = Int((lngEnd - lngStart) / 60) & ":" & (lngEnd - lngStart) Mod 60
MsgBox Duration

[Lberteh]

Thank you so much!

Works perfectly!!!!!!