Calculating Time Difference Exceeding 24 hours

Question

I am having an issue where I am trying to calculate the time difference in seconds and then in a report (Access reports) I will sum those seconds and format it into hh:nn:ss.

However, my calculated field that gathers the time difference between the two fields sometimes exceeds 24 hours and thus throws off the time difference.

I am using the DateDiff function --- DateDiff("s",[BeginningTime],[EndingTime])

What should I do when it comes to circumstances where the time exceeds 24 hours?

The two fields, BeginningTime and EndingTime, are stored in the AM/PM format. I don't think that should matter though.

Answer 1

You can use a function like this:

Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String

' Returns count of days, hours and minutes of datTime
' converted to hours and minutes as a formatted string
' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String

  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute

  FormatHourMinute = strHourMinute

End Function

and this expression as ControlSource for your textbox:

=FormatHourMinute([EndingTime]-[BeginningTime])

However (see comments) this simple expression is only valid for dates of positive numeric value which are dates after 1899-12-30.

To cover all dates, you will need a proper method for calculating a timespan, and that can be done using this function:

' Converts a date value to a timespan value.
' Useful only for date values prior to 1899-12-30 as
' these have a negative numeric value.
'
'   2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateToTimespan( _
    ByVal Value As Date) _
    As Date

    ConvDateToTimespan Value

    DateToTimespan = Value

End Function


' Converts a date value by reference to a linear timespan value.
' Example:
'
'   Date     Time  Timespan      Date
'   19000101 0000  2             2
'
'   18991231 1800  1,75          1,75
'   18991231 1200  1,5           1,5
'   18991231 0600  1,25          1,25
'   18991231 0000  1             1
'
'   18991230 1800  0,75          0,75
'   18991230 1200  0,5           0,5
'   18991230 0600  0,25          0,25
'   18991230 0000  0             0
'
'   18991229 1800 -0,25         -1,75
'   18991229 1200 -0,5          -1,5
'   18991229 0600 -0,75         -1,25
'   18991229 0000 -1            -1
'
'   18991228 1800 -1,25         -2,75
'   18991228 1200 -1,5          -2,5
'   18991228 0600 -1,75         -2,25
'   18991228 0000 -2            -2
'
'   2015-12-15. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub ConvDateToTimespan( _
    ByRef Value As Date)

    Dim DatePart    As Double
    Dim TimePart    As Double

    If Value < 0 Then
        ' Get date (integer) part of Value shifted one day
        ' if a time part is present as -Int() rounds up.
        DatePart = -Int(-Value)
        ' Retrieve and reverse time (decimal) part.
        TimePart = DatePart - Value
        ' Assemble date and time part to return a timespan value.
        Value = CDate(DatePart + TimePart)
    Else
        ' Positive date values are identical to timespan values by design.
    End If

End Sub

Then your expression will look like:

=FormatHourMinute(DateToTimespan([EndingTime])-DateToTimespan([BeginningTime]))

which for Gord's example values, #1899-12-28 01:00:00# and #1899-12-27 23:00:00#, will return 2:00.