VBA – Check Regular & Overtime Hours – Excel functions
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
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.houstoncomputerclasses.com/excel-classes/
