Pro workbook: Week, Coverage, Payroll, Setup, Formulas. Sample data, real formulas.
| Shift rota | ||||||||||
| One code per person per day — M morning, E evening, N night, D double, O off. Hours and headcount are calculated. | ||||||||||
| Week starting | Sep 2026 | |||||||||
| Name | Rate/h | 2026-09-14 | 2026-09-15 | 2026-09-16 | 2026-09-17 | 2026-09-18 | 2026-09-19 | 2026-09-20 | Hours | Pay |
| Amina | 18.50 | M | M | M | M | M | O | O | 40 | 740.00 |
| Ben | 16.00 | E | E | E | E | O | O | E | 40 | 640.00 |
| Chloé | 16.00 | M | M | O | O | M | M | M | 40 | 640.00 |
| Daniel | 21.00 | N | N | N | O | O | N | N | 40 | 840.00 |
| Eva | 16.00 | E | E | O | E | E | E | O | 40 | 640.00 |
| Farid | 18.50 | M | O | M | M | M | O | M | 40 | 740.00 |
| Grace | 16.00 | O | E | E | E | E | E | O | 40 | 640.00 |
| Hiro | 24.00 | D | O | M | M | O | M | E | 48 | 1,152.00 |
| On shift per day | 7 | 6 | 6 | 6 | 5 | 5 | 5 | 328 | 6,032.00 | |
| Hours per code are on Setup; change them there and every total follows. Over 40 hours turns red. |
| Coverage | ||||||||
| Scheduled against required, per shift per day. Red = short, yellow = over. | ||||||||
| Shift | Required | 2026-09-14 | 2026-09-15 | 2026-09-16 | 2026-09-17 | 2026-09-18 | 2026-09-19 | 2026-09-20 |
| Morning | 3 | 4 | 2 | 3 | 3 | 3 | 2 | 2 |
| Evening | 3 | 3 | 3 | 2 | 3 | 2 | 2 | 2 |
| Night | 1 | 1 | 1 | 1 | 0 | 0 | 1 | 1 |
| Total on shift | 7 | 6 | 6 | 6 | 5 | 5 | 5 | |
| Shifts short this week | 9 | |||||||
| Shifts over | 1 |
| Payroll | |||||
| Hours, overtime above the Setup threshold, and pay per person for the week. | |||||
| Name | Rate/h | Hours | Regular | Overtime | Pay |
| Amina | 18.50 | 40 | 40 | 0 | 740.00 |
| Ben | 16.00 | 40 | 40 | 0 | 640.00 |
| Chloé | 16.00 | 40 | 40 | 0 | 640.00 |
| Daniel | 21.00 | 40 | 40 | 0 | 840.00 |
| Eva | 16.00 | 40 | 40 | 0 | 640.00 |
| Farid | 18.50 | 40 | 40 | 0 | 740.00 |
| Grace | 16.00 | 40 | 40 | 0 | 640.00 |
| Hiro | 24.00 | 48 | 40 | 8 | 1,248.00 |
| Total | 328 | 320 | 8 | 6,128.00 | |
| Overtime cost | 96.00 | ||||
| Average hours | 41.0 |
| Setup | |||||
| Week start, shift codes with hours, staffing required per shift, and the team with hourly rates. | |||||
| Week starting (Monday) | 2026-09-14 | ||||
| Overtime after (h/week) | 40 | ||||
| Overtime multiplier | 1.5 | ||||
| Code | Shift | Starts | Ends | Hours | Required/day |
| M | Morning | 07:00 | 15:00 | 8 | 3 |
| E | Evening | 15:00 | 23:00 | 8 | 3 |
| N | Night | 23:00 | 07:00 | 8 | 1 |
| D | Double | 07:00 | 23:00 | 16 | 0 |
| O | Off | 0 | 0 | ||
| Name | Rate/h | ||||
| Amina | 18.50 | ||||
| Ben | 16.00 | ||||
| Chloé | 16.00 | ||||
| Daniel | 21.00 | ||||
| Eva | 16.00 | ||||
| Farid | 18.50 | ||||
| Grace | 16.00 | ||||
| Hiro | 24.00 |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Week · Hours | SUMPRODUCT(SUMIF(Setup!$A$7:$A$11, C6:I6, Setup!$E$7:$E$11)) | Looks each day's code up in the shift table and adds the hours. SUMIF with a range as its criterion returns one number per day; SUMPRODUCT adds them. Works in Excel 2016, Sheets and Numbers — no XLOOKUP needed. |
| Week · Pay | J6*B6 | Hours × rate, before overtime. Payroll applies the multiplier. |
| Week · On shift per day | COUNTA(C6:C13)-COUNTIF(C6:C13,"O") | Everyone with a code that isn't Off. |
| Coverage · cell | COUNTIF(Week!C$6:C$13,"M")+COUNTIF(Week!C$6:C$13,"D") | How many have that shift that day; a Double covers both morning and evening. |
| Coverage · Shifts short | SUMPRODUCT((C5:I7<B5:B7)*1) | Counts cells where scheduled is below required, across the grid. |
| Payroll · Regular / Overtime | MIN(C5,Setup!$B$4) / =MAX(0,C5-Setup!$B$4) | Hours up to the threshold, and hours above it. |
| Payroll · Pay | D5*B5+E5*B5*Setup!$B$5 | Regular at the rate, overtime at rate × multiplier. |
| Payroll · Overtime cost | SUMPRODUCT(E5:E12,B5:B12)*(Setup!$B$5-1) | The premium only — what overtime costs above plain rate. |