---

MS Access 2003: Calculate and format an elapsed time value

This MSAccess tutorial explains how to calculate and format an elapsed time value in Access 2003 (with screenshots and step-by-step instructions).

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