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
Advertisements