505.369.1650 [email protected]

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.

Employee Time

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:

Employee Hours and Minutes Query

The full expression and the result:

HoursAndMinutes: CalcHoursOrMinutes_
    ("Hours",Sum([TimeClockHours]),Sum([TimeClockMinutes])) & "h " _
    & CalcHoursOrMinutes_
    ("Minutes",Sum([TimeClockHours]),Sum([TimeClockMinutes])) & "m"

Employee Hours and Minutes Result

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.

Code:

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

Download Code:

basCalcHoursOrMinutes