Time Calculation in Excel VBA

Question

I am getting time as 23300000 i.e. hhMMssmm format as string and I want to calculate difference of such two values.

Here hh is hours, MM is minutes, ss is seconds, and mm is 60th of second. Using VBA for Excel 2003

how are you planning to calculate the difference between a date that goes over the next day? Ie. 23:30 and 1:30 the next day? — , Aug 27 '14 at 11:19
No the values will be of same day. so 1:30 will never be there. It will always maximum as `24000000` — Rohit Chaudhari, Aug 27 '14 at 11:21
well, you need some if-else logic for this to work, specially the `mm` part. why dont you show us what you have tried and tell what does not work and then we can help you — , Aug 27 '14 at 11:36
related: http://stackoverflow.com/questions/939230/how-to-get-a-datediff-value-in-milliseconds-in-vba-excel — , Aug 27 '14 at 12:12

score 1 · Accepted Answer · answered Aug 27 '14 at 11:51

This UDF will return the absolute value of the difference in seconds

Public Function tDiff(s1 As String, s2 As String) As Double
'
'   calculates the absolute value of the differences
'   returns the answer in seconds
'
    Dim hrs As Double, mins As Double, secs As Double, sixt As Double
    Dim tVal1 As Double, tVal2 As Double

    hrs = CDbl(Mid(s1, 1, 2)) * 60 * 60
    mins = CDbl(Mid(s1, 3, 2)) * 60
    secs = CDbl(Mid(s1, 5, 2))
    sixt = CDbl(Mid(s1, 7, 2)) / 60
    tVal1 = hrs + mins + secs + sixt

    hrs = CDbl(Mid(s2, 1, 2)) * 60 * 60
    mins = CDbl(Mid(s2, 3, 2)) * 60
    secs = CDbl(Mid(s2, 5, 2))
    sixt = CDbl(Mid(s2, 7, 2)) / 60
    tVal2 = hrs + mins + secs + sixt

    If tVal1 > tVal2 Then
        tDiff = tVal1 - tVal2
    Else
        tDiff = tVal2 - tVal1
    End If
End Function

I am suspecting the OP would like the returned value to also be a string in the same format as the input one ;) — , Aug 27 '14 at 12:18
@mehow I was uncertain of the form or format of the result. I posted one option. If *Rohit* would like another format, I will update my answer to suit. — Gary's Student, Aug 27 '14 at 12:24

Bogey · Answer 2 · 2014-08-27T12:24:48.830

How about something like this:

Public Sub test()
    Dim ms1 As Double
    Dim ms2 As Double

    ms1 = ToSeconds(23142700)
    ms2 = ToSeconds(23311500)

    Debug.Print "Difference between dates in seconds: " & ms2 - ms1
End Sub


Public Function ToSeconds(number As Long) As Double
    Dim hh As Long
    Dim mm As Long
    Dim ss As Long
    Dim ms As Long

    ms = (number Mod (100 ^ 1)) / (100 ^ 0)
    ss = (number Mod (100 ^ 2) - ms) / (100 ^ 1)
    mm = (number Mod (100 ^ 3) - ss * (100 ^ 1) - ms) / (100 ^ 2)
    hh = (number Mod (100 ^ 4) - mm * (100 ^ 2) - ss * (100 ^ 1) - ms) / (100 ^ 3)

    ToSeconds = ms * 1 / 60 + ss + mm * 60 + hh * 60 * 60
End Function

The ToSeconds() function converts your number to seconds, and you can do your calculations based on that.

Fair enough - edited the function to return the number of seconds passed since midnight instead. — Bogey, Aug 27 '14 at 12:25

score 0 · Answer 3 · answered Jul 06 '15 at 14:42

While this solution may not be as short as the others, I believe it is very easy to understand. Not everything here may be necessary, but you may find some of it useful in the future.

The run sub routine allows you to run the test function with your specified values.
The test function tests the timeDiff & timeSum logic.
The timeDiff function finds the time-difference between t1 and t0.
The timeSum function finds the time-sum of t1 and t0.
The asDuration function removes the AM/PM suffix from a time value.
The asMilitary function converts 12-hour format to 24 hour-format.
The concat function I created to more easily concatenate strings.

Sub Main() 'Run Test
    MsgBox Test("0:29:0", "23:30:0")
End Sub

Function Test(startT As Date, endT As Date) 'Test timeDiff & timeSum logic
    Dim nextShift As Date, prevShift As Date, hours As Date

    hours = timeDiff(endT, startT)
    prevShift = timeDiff(startT, "0:30:0")
    nextShift = timeSum("0:30:0", endT)        

    Test = concat("Start -", startT, "", "End - ", endT, "", "Duration -", asDuration(hours), "", "Next Shift: ", nextShift, "", "Prev Shift: ", prevShift)
End Function


Function timeDiff(t1 As Date, t0 As Date) As Date 'Return Time1 minus Time0
    Dim units(0 To 2) As String

    units(0) = Hour(t1) - Hour(t0)
    units(1) = Minute(t1) - Minute(t0)
    units(2) = Second(t1) - Second(t0)

    If units(2) < 0 Then
        units(2) = units(2) + 60
        units(1) = units(1) - 1
    End If

    If units(1) < 0 Then
        units(1) = units(1) + 60
        units(0) = units(0) - 1
    End If

    units(0) = IIf(units(0) < 0, units(0) + 24, units(0))
    timeDiff = Join(units, ":")
End Function


Function timeSum(t1 As Date, t0 As Date) As Date 'Return Time1 plus Time0
    Dim units(0 To 2) As String

    units(0) = Hour(t1) + Hour(t0)
    units(1) = Minute(t1) + Minute(t0)
    units(2) = Second(t1) + Second(t0)

    If units(2) >= 60 Then
        units(2) = units(2) Mod 60
        units(1) = units(1) + 1
    End If

    If units(1) >= 60 Then
        units(1) = units(1) Mod 60
        units(0) = units(0) + 1
    End If

    units(0) = IIf(units(0) >= 24, units(0) Mod 24, units(0))
    timeSum = Join(units, ":")
End Function


Function asDuration(time As Date) As String 'Display as duration; Remove AM/PM suffix from time
    time = asMilitary(time)
    asDuration = Left(time, Len(time))
End Function


Function asMilitary(time As Date) As String 'Convert 12-hour format to 24-hour-format
    asMilitary = Hour(time) & ":" & Minute(time) & ":" & Second(time)
End Function


Function concat(ParamArray var() As Variant) As String 'Return arguments of function call concatenated as a single string
    For Each elem In var()
        concat = IIf(elem <> "", concat & elem & " ", concat & vbNewLine)
    Next
End Function

Time Calculation in Excel VBA

3 Answers3