Almost every rota in the world starts life in a spreadsheet, and that is fine. The trouble with most templates you find online is that they are grids to fill in by hand: nice to look at, and then you count the hours yourself.

This one does a bit more. It is a weekly template that works out the hours on its own, copes with shifts that end after midnight and compares the total against contracted hours.

Download the weekly shift template (.xlsx)

No sign-up, no email. It opens in Excel, LibreOffice and Google Sheets.

How it is built

One row per person, seven days across. Each day has three cells you fill in and one that calculates itself:

  • Start and End, in 9:00 and 17:30 format;
  • Break, in minutes of unpaid break (0 if there is none);
  • Hours, worked out by the sheet.

At the end of the row you get the weekly total, the contracted hours and the difference, with a plus or minus sign. Below the grid there is a per-day total row, which is what lets you see at a glance where you have too many people and where too few.

The formula doing the work is this one:

=IF(OR(B5="",C5=""),"",ROUND(MOD(C5-B5,1)*24-N(D5)/60,2))

The interesting part is MOD. It is what keeps the sum right when the end time is smaller than the start time, which is what happens when a shift crosses midnight. A 22:00 to 06:00 shift would give minus sixteen hours with a plain C5-B5; with MOD(...,1) it gives eight. This is the number one bug in home-made rota sheets, and it usually surfaces when someone checks their payslip.

Using it without getting hurt

Five habits that separate a sheet that holds from one you rebuild every month.

Type times as times. If you type 9 instead of 9:00, Excel reads the number nine, not nine in the morning, and the sum breaks silently. The cells are pre-formatted, but the first paste from another file is where the format gets lost.

One copy per week, not one endless file. Duplicate the sheet and change the Monday date. The file holding three months of overwritten weeks is the file where nobody can reconstruct what actually happened.

The break column is unpaid break only. If your contract treats it as working time, put zero: the sheet counts payable hours, not minutes of rest.

Fill in contracted hours for part-timers too. It is the only way the Difference column says anything useful: without that number the weekly total has nothing to be compared against.

Log real hours, not planned ones. The plan says what you intended to work, the record says what you worked. They are two different files, and merging them is the fastest route to a month-end argument. The full method is in how to calculate worked hours, and for a quick sum there is the worked hours calculator.

What the sheet cannot do

Worth saying plainly, even though we write software that does this for a living: for a team of three or four people on stable hours, this template is plenty.

What no spreadsheet does is know the rules. Excel adds up, it does not know that a sequence breaks the 11 hours of rest between shifts, that a run of weeks pushes past the 48-hour average, or that nobody on Saturday can cash up. It will never tell you: you find out on Saturday.

And it does not do the second thing you actually need: deciding. The sheet records the decisions you already made, it does not make them. With ten people, mixed contracts, changing availability and constraints to respect, deciding stays hours of manual work every week.

The signals that a spreadsheet has stopped being enough are lined up in Excel for rotas: how long it holds.

When the template starts feeling tight, Sked Solve does the part the sheet is missing: it knows the constraints and builds the week. See how it works at Sked Solve.