Hello Everyone,
I know this topic has been covered several times but I have not found the answer within the current threads...... I am trying to build a spreadsheet that will take the error out of calculating bi-weekly overtime.
The yellow and green areas are my biggest issue currently. What I am trying to get is this. If the previous week of the two pay periods did NOT end on the last day of the pay period that the information in the last week of the period will carry over and auto-populate onto the next sheet then back out days paid on the previous pay period leaving only the regular hours for the current period and the OT hours for hours worked over 40.
For anyone not familiar with payroll, this is how it woks. Overtime is calculated based on 40 hours WORKED in a declared week. In my case it is a calendar week. SO, for example, if the end of the month falls on a Tuesday, then the previous week hours from Tuesday - Saturday get carried forward in order to determine overtime.
Example:
June 30 fell on a Wednesday therefore, there is a carryover of hours to determine the OT worked for an employee. The week of 06/27-07/03 is needed to determine if the employee worked over 40 hours. If so, hours over 40 will be paid at an OT rate. The regular hours worked from 06/27-06/30 will have been paid already on the previous period so the employee would be paid for regular hours worked on Thursday 07/01, Friday 07/2 and Saturday 07/03.
In this example the employee worked 48 hours this week, 24 of those hours were paid on the previous period so he would be paid 24 hours at a regular rate and 8 hours at an overtime rate for this week.
This is what I am trying to figure our how to create in Excel...... THANK YOU in advance!!!!
I know this topic has been covered several times but I have not found the answer within the current threads...... I am trying to build a spreadsheet that will take the error out of calculating bi-weekly overtime.
The yellow and green areas are my biggest issue currently. What I am trying to get is this. If the previous week of the two pay periods did NOT end on the last day of the pay period that the information in the last week of the period will carry over and auto-populate onto the next sheet then back out days paid on the previous pay period leaving only the regular hours for the current period and the OT hours for hours worked over 40.
For anyone not familiar with payroll, this is how it woks. Overtime is calculated based on 40 hours WORKED in a declared week. In my case it is a calendar week. SO, for example, if the end of the month falls on a Tuesday, then the previous week hours from Tuesday - Saturday get carried forward in order to determine overtime.
Example:
| Sunday 06/27 | Monday 06/28 | Tuesday 06/29 | Wednesday 06/30 | Thursday 07/01 | Friday 07/02 | Saturday 07/03 |
| Hours worked 0 | Hours worked 8 | Hours worked 8 | Hours worked 8 | Hours worked 8 | Hours worked 8 | Hours worked 8 |
June 30 fell on a Wednesday therefore, there is a carryover of hours to determine the OT worked for an employee. The week of 06/27-07/03 is needed to determine if the employee worked over 40 hours. If so, hours over 40 will be paid at an OT rate. The regular hours worked from 06/27-06/30 will have been paid already on the previous period so the employee would be paid for regular hours worked on Thursday 07/01, Friday 07/2 and Saturday 07/03.
In this example the employee worked 48 hours this week, 24 of those hours were paid on the previous period so he would be paid 24 hours at a regular rate and 8 hours at an overtime rate for this week.
This is what I am trying to figure our how to create in Excel...... THANK YOU in advance!!!!
| TimeSheet-Calculator_-2021_v1.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | |||
| 7 | PREVIOUS PERIOD (For OT Calculations ONLY) | ||||||||||
| 8 | Year | Month | Date | Weekend | |||||||
| 9 | 2021 | June | 27 | Sat & Sun | |||||||
| 10 | |||||||||||
| 11 | Year | Month | Date | Weekend | |||||||
| 12 | 2021 | June | 1 | Sat & Sun | |||||||
| 13 | |||||||||||
| 14 | |||||||||||
| 15 | Start Time | Regular Hours | Regular Pay (hourly) | Comp TIme (hourly x1.5) | |||||||
| 16 | 8:00 | 8 | |||||||||
| 17 | |||||||||||
| 18 | Hours Previously Paid to Determine OT / Comp Time | ||||||||||
| 19 | |||||||||||
| 20 | |||||||||||
| 21 | Day | Date | In Time | Out Time | Break Hrs | Regular Hrs | OT Hours | Total Pay | |||
| 22 | Fri | 27 | 9:00 AM | 6:00 PM | 1.0 | 6.0 | 2.0 | 0.0 | |||
| 23 | Sat | 28 | |||||||||
| 24 | Sun | 29 | |||||||||
| 25 | Mon | 30 | |||||||||
| 26 | Tue | 31 | |||||||||
| 27 | Wed | 1 | |||||||||
| 28 | Thu | 2 | |||||||||
| 29 | |||||||||||
| 30 | Weekly Summary | 1.0 | 6.0 | 2.0 | 0.0 | ||||||
| 31 | |||||||||||
| 32 | CURRENT PERIOD PAYROLL | ||||||||||
| 33 | |||||||||||
| 34 | Day | Date | In Time | Out Time | Break Hrs | Regular Hrs | OT Hours | Total Pay | |||
| 35 | Sun | 1 | 9:00 AM | 6:00 PM | 1.0 | 6.0 | 2.0 | 0.0 | |||
| 36 | Mon | 2 | |||||||||
| 37 | Tue | 3 | |||||||||
| 38 | Wed | 4 | |||||||||
| 39 | Thu | 5 | |||||||||
| 40 | Fri | 6 | |||||||||
| 41 | Sat | 7 | |||||||||
| 42 | Sun | 8 | |||||||||
| 43 | Mon | 9 | |||||||||
| 44 | Tue | 10 | |||||||||
| 45 | Wed | 11 | |||||||||
| 46 | Thu | 12 | |||||||||
| 47 | Fri | 13 | |||||||||
| 48 | Sat | 14 | |||||||||
| 49 | Sun | 15 | |||||||||
| 50 | |||||||||||
| 51 | |||||||||||
| 52 | Bi-weekly Summary | 1.0 | 6.0 | 2.0 | 0.0 | ||||||
| 53 | |||||||||||
July Week 1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B22,B50 | B22 | =C22 |
| C22 | C22 | =$E$9 |
| B23:C28,B36:C49 | B23 | =B22+1 |
| G22:G28,G35:G50 | G22 | =IFERROR(IF(AND(D22<>"",E22<>""),IF(D22>$C$16+TIME($D$16,($D$16-INT($D$16))*60,0),0,IF(E22>$C$16+TIME($D$16,($D$16-INT($D$16))*60,0),MIN(TIME($D$16,($D$16-INT($D$16))*60,0),($C$16+TIME($D$16,($D$16-INT($D$16))*60,0)-D22)),MIN(IF((E22-$C$16)<0,0,(E22-$C$16)),(E22-D22))))*24,"")-F22,"") |
| H22:H28,H35:H50 | H22 | =IF(AND(D22<>"",E22<>""),((IF(D22<$C$16,MIN($C$16-D22,E22-D22),0)+IF(E22>$C$16+TIME($D$16,($D$16-INT($D$16))*60,0),MIN((E22-$C$16-TIME($D$16,($D$16-INT($D$16))*60,0)),(E22-D22)),0))*24),"") |
| I22:I28,I35:I50 | I22 | =IFERROR(G22*IF(AND(ISNUMBER(SEARCH(TEXT(B22,"ddd"),$F$9)),'OT Data'!$F$2),$F$16,$E$16)+H22*$F$16,"") |
| F30:I30 | F30 | =SUM(F22:F28) |
| B35 | B35 | =DATE($C$9,MATCH($D$9,'OT Data'!$B$2:$B$13,0),'July Week 1'!$E$9) |
| C35 | C35 | =$E$12 |
| C50 | C50 | =IF(C49=15,"",C49+1) |
| F52:I52 | F52 | =SUM(F35:F48) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B50:I50 | Cell | contains a blank value | text | NO |
| B35:I50,B22:I28 | Expression | =ISNUMBER(SEARCH(TEXT($B22,"ddd"),$F$9)) | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D35:D50 | Custom | =AND($D35<=1,ISNUMBER($D35)) |
| E35:E37 | Custom | =AND($E35<=1,ISNUMBER($E35),($E35>=$D35)) |
| E38:E50 | Custom | =AND($E38<=1,ISNUMBER($E38),($E38>$D38)) |
| D22:D28 | Custom | =AND($D22<=1,ISNUMBER($D22)) |
| E22:E28 | Custom | =AND($E22<=1,ISNUMBER($E22),($E22>=$D22)) |
| C9:C10 | List | ='OT Data'!$A$2:$A$12 |
| D9:D10 | List | ='OT Data'!$B$2:$B$13 |
| E9:E10 | List | =DateCalc |
| F9:F10 | List | ='OT Data'!$D$2:$D$16 |
| C12:C13 | List | ='OT Data'!$A$2:$A$12 |
| D12:D13 | List | ='OT Data'!$B$2:$B$13 |
| E12:E13 | List | =DateCalc |
| F12:F13 | List | ='OT Data'!$D$2:$D$16 |
