## 8/16/2012

### Calculate and format an elapsed time value in Access

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
'Determine month portion
If Day(pEnd) < Day(pStart) Then
LMonths = DateDiff("m", LDate, pEnd) - 1
Else
LMonths = DateDiff("m", LDate, pEnd)
End If
'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