Confident Cells

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.

Your turn

In I2, add up each person's hours for the whole week, Monday to Sunday. "Off" means no shift that day.

ABCDEFGHI
1EmployeeMonTueWedThuFriSatSunTotal hours
2Rachel446104.5910
3Nia7107.58Off69.5
4Ollie69.587107.55
5Hannah9758Off4.59.5
6Priya7.584.5Off776.5
7Lucy9.59Off8OffOff4
8MarekOffOff476.56.5Off
9Nadia6.597Off7.58Off

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

ABCDEFGHI
1NameMonTueWedThuFriHoursContract30
2Amira8886
3Ben7.57.57.57.57.5
4Cara444
  1. =SUM(B2:F2) in G2. Hours for the week: Amira 30, Ben 37.5, Cara 12.
  2. =COUNT(B2:F2) in J2. Amira has a day off on Wednesday, so COUNT gives 4 days worked.
  3. =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

  1. In I2, add up each person's hours for the whole week, Monday to Sunday. "Off" means no shift that day.
  2. In I2, count how many shifts each person works this week. Don't count the days marked "Off".
  3. 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.
  4. 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.
  5. In H2, count how many people clocked in after the start time in H1. Clocking in exactly on time isn't late.