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/