Guided project · 6 steps Cellwise Plus
Weekly overtime check in Excel
Split each person's week into regular and overtime hours and see what overtime costs.
You will use: MIN, MAX, ROUND, SUM, COUNTIF, SUMPRODUCT
Starting data
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Hours worked | Hourly rate | Regular hrs | Overtime hrs | Gross pay | Overtime after (hrs/week) | 40 | |
| 2 | Ana | 38 | 22.5 | Overtime multiplier | 1.5 | ||||
| 3 | Ben | 44.5 | 24 | ||||||
| 4 | Chloe | 40 | 21 | Total overtime hrs | |||||
| 5 | Dev | 47 | 26.5 | People with overtime | |||||
| 6 | Eli | 36.5 | 19.75 | Overtime share of pay | |||||
| 7 | Fay | 41.25 | 23 |
Build it step by step
Step 1
Regular hours are the hours up to the threshold in I1 (your policy and local rules set it). Write it in D2 and fill to D7.
Step 2
Overtime hours are anything above the threshold, never negative. Write it in E2 and fill to E7.
Step 3
Gross pay: regular hours at the rate, plus overtime hours at the rate times the multiplier in I2, rounded to cents. Write it in F2 and fill to F7.
Step 4
Total overtime hours for the team in I4.
Step 5
How many people worked overtime? Write it in I5.
Step 6
What share of total gross pay was overtime pay? Overtime pay is overtime hours times rate times the multiplier, added up. Write it in I6.