Guided project · 6 steps Cellwise Plus
Timesheet and overtime pay in Excel
Split hours into regular and overtime, pay everyone correctly, and see where overtime piles up.
You will use: MIN, MAX, ROUND, SUM, COUNTIF, SUMIF
Starting data
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Dept | Rate | Hours | Regular | Overtime | Pay | OT after (hrs) | 40 | |
| 2 | Rosa Diaz | Warehouse | 21.5 | 46.5 | OT multiplier | 1.5 | ||||
| 3 | Tom Becker | Warehouse | 19.75 | 40 | ||||||
| 4 | Aisha Khan | Front desk | 18 | 32.5 | Total payroll | |||||
| 5 | Luis Ortega | Warehouse | 22 | 51 | Staff with OT | |||||
| 6 | Mei Chen | Front desk | 18.5 | 41.25 | Warehouse OT hrs | |||||
| 7 | Jake Turner | Delivery | 20 | 38 |
Build it step by step
Step 1
Regular hours stop at the overtime line in J1. Write regular hours in E2, fill to E7.
Step 2
Overtime is any hours over J1. Write it in F2, fill to F7. Nobody should get negative overtime.
Step 3
Pay is rate times regular hours, plus rate times the multiplier in J2 times overtime, rounded to cents. Write it in G2, fill to G7.
Step 4
What is the total payroll for the week? Put it in J4.
Step 5
How many people worked any overtime? Put it in J5.
Step 6
How many overtime hours did the Warehouse rack up? Put it in J6.