VBA – Check Regular & Overtime Hours – Excel functions

/in , , /by

Below are 2 Excel VBA UDFs to check and divide regular and overtime hours based on 40 hour week, Monday through Friday Payroll.

Date Hours Worked Regular Overtime
12/15/15 14 14 0
12/16/15 9 9 0
12/17/15 11 11 0
12/18/15 9 6 3
12/19/15 7 0 7
12/20/15 8 0 8
12/21/15 5 5 0
12/22/15 2 2 0
12/23/15 1 1 0
12/24/15 1 1 0
12/25/15 6 6 0
12/26/15 7 7 0
12/27/15 14 14 0
12/28/15 9 9 0
12/29/15 9 9 0
12/30/15 9 9 0
12/31/15 9 9 0
1/1/16 8 4 4
1/2/16 9 0 9
1/3/16 13 0 13
1/4/16 15 15 0
1/5/16 10 10 0 
Public Function REG(datecol As Range, hourscol As Range)
hrs = 0
For i = 2 To datecol.Row

   dt = Application.WeekNum(Cells(i, datecol.Column), 2)
   cdt = Application.WeekNum(Cells(datecol.Row, datecol.Column), 2)

   If dt = 53 Then
    dt = 1
   End If
   If cdt = 53 Then
    cdt = 1
   End If

   If cdt = dt Then
        hrs = hrs + Cells(i, hourscol.Column).Value

   End If

   If hrs <= 40 Then         REG = Cells(datecol.Row, hourscol.Column)    Else         If (40 - (hrs - Cells(datecol.Row, hourscol.Column))) > 0 Then
            REG = 40 - (hrs - Cells(datecol.Row, hourscol.Column))
        Else
            REG = 0
        End If
   End If
Next i

End Function

Public Function OVT(datecol As Range, hourscol As Range)
hrs = 0
For i = 2 To datecol.Row

   dt = Application.WeekNum(Cells(i, datecol.Column), 2)
   cdt = Application.WeekNum(Cells(datecol.Row, datecol.Column), 2)

   If dt = 53 Then
    dt = 1
   End If
   If cdt = 53 Then
    cdt = 1
   End If

   If cdt = dt Then
        hrs = hrs + Cells(i, hourscol.Column).Value

   End If

   If hrs > 40 Then
        If (hrs - Cells(datecol.Row, hourscol.Column)) > 40 Then
            OVT = Cells(datecol.Row, hourscol.Column)
        Else
         OVT = hrs - 40
        End If
   Else

    OVT = 0
   End If
Next i

End Function

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/