Rotas and timesheets: practice exercises
Weekly hours, shift counts, overtime, pay, late starts. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In I2, add up each person's hours for the whole week, Monday to Sunday. "Off" means no shift that day.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Total hours |
| 2 | Rachel | 4 | 4 | 6 | 10 | 4.5 | 9 | 10 | |
| 3 | Nia | 7 | 10 | 7.5 | 8 | Off | 6 | 9.5 | |
| 4 | Ollie | 6 | 9.5 | 8 | 7 | 10 | 7.5 | 5 | |
| 5 | Hannah | 9 | 7 | 5 | 8 | Off | 4.5 | 9.5 | |
| 6 | Priya | 7.5 | 8 | 4.5 | Off | 7 | 7 | 6.5 | |
| 7 | Lucy | 9.5 | 9 | Off | 8 | Off | Off | 4 | |
| 8 | Marek | Off | Off | 4 | 7 | 6.5 | 6.5 | Off | |
| 9 | Nadia | 6.5 | 9 | 7 | Off | 7.5 | 8 | Off |
How it works
A rota grid has a row for each person and a column for each day. SUM adds the hours, and COUNT counts only the cells with numbers, so "Off" isn't counted as a shift. COUNTA would count it.
Overtime is the hours over the contract, but never less than 0: =MAX(0,B2-$H$1). Times can be criteria too: ">"&H1 with a start time in H1.
=SUM(B4:F4) =COUNT(B4:F4) =MAX(0,G4-$I$1) =COUNTIF(G2:G4,">="&I1)
Adding up a week
A rota has a row for each person and a column for each day. SUM across the row gives their hours, and COUNT gives the number of days they worked: it counts the cells with numbers and skips the empty days off.
Worked example
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | Mon | Tue | Wed | Thu | Fri | Hours | Contract | 30 |
| 2 | Amira | 8 | 8 | 8 | 6 | ||||
| 3 | Ben | 7.5 | 7.5 | 7.5 | 7.5 | 7.5 | |||
| 4 | Cara | 4 | 4 | 4 |
=SUM(B2:F2)in G2. Hours for the week: Amira 30, Ben 37.5, Cara 12.=COUNT(B2:F2)in J2. Amira has a day off on Wednesday, so COUNT gives 4 days worked.=MAX(0,SUM(B3:F3)-$I$1)in J3. Overtime is hours over the 30-hour contract in I1. Ben has 7.5. MAX(0, ...) gives 0, not a minus figure, for anyone under 30.
Overtime pay and counting
- When overtime pays time and a half, split the hours: normal hours × rate, plus overtime hours × rate × 1.5.
- To count people over a limit kept in a cell, join the sign to the cell with &:
=COUNTIF(G2:G4,">"&I1). Putting I1 inside the quotes would look for the text "I1".
What you'll practise
- In I2, add up each person's hours for the whole week, Monday to Sunday. "Off" means no shift that day.
- In I2, count how many shifts each person works this week. Don't count the days marked "Off".
- Contracted hours are in H1. In C2, work out each person's overtime: the hours they worked over their contract, or 0 if they didn't go over.
- Basic hours are paid at the rate in H1, and overtime hours (column C) at time and a half. In D2, work out each person's pay. Column B includes the overtime hours.
- In H2, count how many people clocked in after the start time in H1. Clocking in exactly on time isn't late.