How to find the difference between two “times” on the same day or on two different days…
Posted on September 12, 2011
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 'Date & Time - How to find the difference between two "times" on the same day or on two different days... 'i.e. time difference between: ' 11:30:00 on 03/01/01 ' 22:00:20 on 04/01/01 ' 'Please use only 24 hr. format Public Function TimeDiff(LesserTime As String, LesserTimeDate As String, GreaterTime As String, _ GreaterTimeDate As String) As String Dim Thrs As Single, Tmins As Single, Tsecs As Single, ReturnTime As String Dim DTcheck As Single DTcheck = DateDiff("d", LesserTimeDate, GreaterTimeDate) If DTcheck > 0 Then For i = 0 To DTcheck Hour(GreaterTime) = Hour(GreaterTime) + 24 Next ElseIf DTcheck < 0 Then TimeDiff = "ERROR" Exit Function End If Thrs = Hour(GreaterTime) - Hour(LesserTime) If Thrs > 0 Then Tmins = Minute(GreaterTime) - Minute(LesserTime) If Tmins < 0 Then Tmins = Tmins + 60 Thrs = Thrs - 1 End If ElseIf Thrs = 0 And Minute(GreaterTime) > Minute(LesserTime) Then Tmins = Minute(GreaterTime) - Minute(LesserTime) Else TimeDiff = "ERROR" Exit Function End If Tsecs = Second(GreaterTime) - Second(LesserTime) If Tsecs < 0 Then Tsecs = Tsecs + 60 If Tmins > 0 Then Tmins = Tmins - 1 Else Tmins = 59 End If TimeDiff = TimeSerial(Thrs, Tmins, Tsecs) End Function |