A reader’s method for calculating total hours.
Bill Patterson responded to my request in Access Archon #129for alternate (possibly simpler) methods of calculating total hours. I used a series of calculated fields in a query to do the calculations; by contrast, the Nearest_Time function listed below does all the calculations in one place, and can be used in code:
Public Function Nearest_Time(ByVal at_Start As Date, _
ByVal at_Endof As Date, _
ByVal ai_Interval As Integer) _
As Double
‘ The following expression will calculate and return a string that depicts
‘ the Hours and the next upper limit of the specified Interval
‘ eg. 01:15
‘ Nearest_Time = Format$(DateDiff(“n”, at_Start, at_Endof) 60, “00”) & _
‘ “:” & _
‘ Format$(ai_Interval * (((DateDiff(“n”, at_Start, at_Endof) Mod 60) ai_Interval + 1)), “00”)
‘ The following will return the above value as 1.25 hours
Dim li_Hours As Integer
Dim li_Minutes As Integer
li_Hours = Val(Format$(DateDiff(“n”, at_Start, at_Endof) 60, “00”))
If ai_Interval > 0 And ai_Interval <= 60 Then
li_Minutes = Val(Format$(ai_Interval _
* (((DateDiff(“n”, at_Start, at_Endof) Mod 60) _
ai_Interval + 1)), “00”))
Else
li_Hours = li_Hours + 1 ‘ Rounded up to the Next Hour
li_Minutes = 0
End If
Nearest_Time = li_Hours + li_Minutes / 60
End Functionon
When I tested this function in the Immediate window, using the string
?Nearest_Time(#2/2/2005 10:11am#, #2/4/2005 2:35pm#, 15)
I got the result 52.5.
