Access: Calculate and format an elapsed time value in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, how do I format a time on a report so it appears as elapsed hours and minutes?

For example, 27 hours and 31 minutes appears as 27:31. I can do this for values up to 24 hours. But for anything over 24 hours, the full day(s) are dropped off.

Answer: A "elapsed time" is usually calculated by taking the difference between two dates. So, you will need to store the "from" and "to" dates separately and then calculate the elapsed time.

We've created a function that accepts two dates and returns the formatted elapsed time.

You can paste the following function in an Access module and then reference it like any other Access function.

Public Function GetElapsedTime(pStart As Date, pEnd As Date) As String

    Dim LDate As Date
    Dim LYears As Integer
    Dim LMonths As Integer

    Dim LInterval As Double
    Dim Totalhours As Long
    Dim Totalminutes As Long
    Dim LDays As Long
    Dim LHours As Long
    Dim LMinutes As Long

    Dim LDisplay As String
    Dim LMthDisplay As String
    Dim LDayDisplay As String

    'Determine year portion of interval
    If Month(pEnd) < Month(pStart) Or (Month(pEnd) = Month(pStart) And Day(pEnd) < Day(pStart)) Then
        LYears = Year(pEnd) - Year(pStart) - 1
    Else
        LYears = Year(pEnd) - Year(pStart)
    End If

    LDate = DateAdd("yyyy", LYears, pStart)

    'Determine month portion
    If Day(pEnd) < Day(pStart) Then
        LMonths = DateDiff("m", LDate, pEnd) - 1
    Else
        LMonths = DateDiff("m", LDate, pEnd)
    End If

    LDate = DateAdd("m", LMonths, LDate)

    'Determine the elapsed days, hours, minutes, seconds portion
    LInterval = pEnd - LDate

    LDays = Int(CSng(LInterval))
    Totalhours = Int(CSng(LInterval * 24))
    Totalminutes = Int(CSng(LInterval * 1440))
    LHours = Totalhours Mod 24
    LMinutes = Totalminutes Mod 60

    'Pluralize months?
    If LMonths = 1 Then
        LMthDisplay = LMonths & " month, "
    Else
        LMthDisplay = LMonths & " months, "
    End If

    'Pluralize days?
    If LDays = 1 Then
        LDayDisplay = LDays & " day, "
    Else
        LDayDisplay = LDays & " days, "
    End If

    'Format display of results
    If LYears = 0 Then
        If LMonths = 0 Then
            If LDays = 0 Then
                LDisplay = Right("00" & LHours, 2)
                LDisplay = LDisplay & ":" & Right("00" & LMinutes, 2)
            Else
                LDisplay = LDayDisplay & Right("00" & LHours, 2)
                LDisplay = LDisplay & ":" & Right("00" & LMinutes, 2)
            End If
        Else
            LDisplay = LMthDisplay & LDayDisplay & Right("00" & LHours, 2)
            LDisplay = LDisplay & ":" & Right("00" & LMinutes, 2)
        End If
    Else
        If LYears = 1 Then
            LDisplay = LYears & " year, " & LMthDisplay & LDayDisplay
            LDisplay = LDisplay & Right("00" & LHours, 2) & ":" & Right("00" & LMinutes, 2)
        Else
            LDisplay = LYears & " years, " & LMthDisplay & LDayDisplay
            LDisplay = LDisplay & Right("00" & LHours, 2) & ":" & Right("00" & LMinutes, 2)
        End If
    End If

    'Return the full elapsed value
    GetElapsedTime = LDisplay

End Function