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:
https://www.chicagocomputerclasses.com/excel-classes/