Calculate time difference for date picker content control

Question

I am trying to determine the time difference between 2 separate date picker content controls in word-vba. The result is shown in a plain text content control. I am currently able to produce a result but having a hard time producing the time difference in "h:mm:ss" format. Any help will be appreciated

Answer 1

As VBA does not have a built-in function to format a timespan, you need to calculate the individual components such as days, hours, minutes, seconds yourself, e.g. using the following function:

Public Function FormattedDateDiff(date1 As ContentControl, date2 As ContentControl) As String

    Dim dateDiffInSeconds As Long
    Dim dd As Long
    Dim hh As Integer
    Dim mm As Integer
    Dim ss As Integer
    Dim ss_remaining As Long

    dateDiffInSeconds = Abs(dateDiff("s", date1.Range.Text, date2.Range.Text))

    dd = dateDiffInSeconds \ 86400
    ss_remaining = dateDiffInSeconds Mod 86400

    hh = ss_remaining \ 3600
    ss_remaining = ss_remaining Mod 3600

    mm = ss_remaining \ 60
    ss = ss_remaining Mod 60

    If dd > 0 Then
        FormattedDateDiff = dd & " days, "
    End If

    FormattedDateDiff = FormattedDateDiff & TimeSerial(hh, mm, ss_remaining)

End Function