This function can be used to return total hours or total minutes from a column of Hours and a column of Minute values. For example, in the table below, an Hours value was captured separately from a Minutes value in a time clock application. If the columns are just summed, some of the Minutes should be converted to Hours and added to the Hours column.
To display a sum of those columns and keep the hours and minutes format, the CalcHoursOrMinutes() function is used twice; once for creating the hours value and once for creating the minutes value. The expression to combine the values in a query would look like this:
The full expression and the result:
HoursAndMinutes: CalcHoursOrMinutes_ ("Hours",Sum([TimeClockHours]),Sum([TimeClockMinutes])) & "h " _ & CalcHoursOrMinutes_ ("Minutes",Sum([TimeClockHours]),Sum([TimeClockMinutes])) & "m"
The function was designed to calculate Hours and Minutes separately so that the unit descriptions (h, Hr., m, Min. etc.) could vary and be added later. If the unit descriptions are known, then the CalcType argument could be eliminated and the function could be changed to return the complete expression in one function call.
If the Hours and Minutes columns need to remain separate, the function could be called in one column for AdjustedHours and another column for AdjustedMinutes.
Public Function CalcHoursOrMinutes(CalcType As String, Hours, Minutes) ' This procedure calculates the values for the CalcType, "Hours" or "Minutes". ' If either Hours or Minutes is Null, the function returns Null. On Error GoTo Err_Handler ' CalcHoursOrMinutes() Version 1.0.0 ' Copyright © 2013 Extra Mile Data, www.extramiledata.com. ' For questions or issues, please contact [email protected] ' Use (at your own risk) and modify freely as long as proper credit is given. Dim lngTotalMinutes As Long Dim lngHours As Long Dim lngMinutes As Long ' Clear the values and exit if some of the calculation values are null. If IsNull(Hours) And IsNull(Minutes) Then CalcHoursOrMinutes = Null GoTo Exit_Proc End If ' Get the total minutes. lngTotalMinutes = (Nz(Hours, 0) * 60) + Nz(Minutes, 0) ' Get the hours. lngHours = Int(lngTotalMinutes / 60) ' Get the minutes. lngMinutes = lngTotalMinutes - (lngHours * 60) If CalcType = "Hours" Then CalcHoursOrMinutes = lngHours Else CalcHoursOrMinutes = lngMinutes End If Exit_Proc: On Error Resume Next Exit Function Err_Handler: MsgBox Err.Number & " " & Err.Description, vbInformation, _ "CalcHoursOrMinutes()" CalcHoursOrMinutes = Null Resume Exit_Proc End Function