A monthly rota is up to 31 columns of letters, and an A on Tuesday beside an M on Wednesday looks like any other pair. It means 8 hours off between a 23:00 finish and a 07:00 start, and nothing in the grid says so. This page builds the month in Google Sheets with formulas you can read, runs through seven checks worth doing before you publish it, and is straight about what a spreadsheet rota will not do.
A rota template is four things: a staff list, a shift list, a month of dates with names down the side, and something that checks what you typed.
Plenty of rota templates are Excel workbooks with a line telling you to import them into Google Sheets. This one was built in Google Sheets. Open the free copy, sign in and choose Make a copy, and it goes into your own Drive. You need a Google account and nothing else: no sign-up, no email and no card with us.
It adds a ShiftSheet menu to the menu bar with the rota in four steps, 1. Set up Staff & Shifts, 2. Build this month, 3. Check for problems and 4. Totals, and Timesheet for one person below them. Installed this way it asks for one permission and nothing else: to read and write the spreadsheet it sits in. The whole script is in your copy under Extensions, Apps Script, so you can read what it does.
Or build your own. The rest of this page is that build, one part per section, and each section ends with what the copy does for the same part.
Two short lists, and every formula below reads from them. Same layout as the seven mistakes page, plus the break and rate columns the other two guides add and one cell on the Schedule sheet.
A2:A25 one name per row, D2:D25 a weekly hour limit for anyone who has their own, E2:E25 an hourly rate if you want costs later.
A2:A15 shift code, B start time, C end time, D paid hours (a formula, never typed), E unpaid break in whole minutes. B and C hold real times, not text.
A1 any date in the month, B1:AF1 the dates (a formula), A2:A25 names, B2:AF25 the grid you type codes into.
B2:AF25 the same grid in hours instead of codes, one formula per cell. Nothing on it is typed by hand.
Keep codes short: M 07:00 to 15:00, A 15:00 to 23:00,
N 23:00 to 07:00, each with a 30 minute unpaid break. Codes for days off,
holiday and sickness can leave the times empty and come out as 0 hours. Then in
Shifts!D2:
=ARRAYFORMULA(IF(A2:A15="","",MOD(C2:C15-B2:B15,1)*24-IF(E2:E15="",0,E2:E15)/60))
That is the formula from the night
shift hours page. MOD turns the night's minus 16 into 8 and the break
comes off afterwards, so all three shifts come out at 7.5 hours. Every total on
this page is built on this column, so read it by eye before going on.
Names must match exactly between Staff and Schedule. Kate on one and Katie on the other are two people, and the one on the grid quietly gets the default limit and a rate of zero. Leave D blank for anyone on the default; the weekly overtime page has the formula that falls back to it.
In the copy, each person can have their own weekly hour limit in the Staff sheet's Max hours / week column, and everyone else gets the default. The Shifts sheet takes an unpaid break in minutes for each code, and unpaid breaks are taken off each shift's hours.
One cell decides which month the tab is. The date row, the weekends and the length of the month all follow from it.
Type any date in October 2026 into Schedule!A1, then in B1:
=SEQUENCE(1,DAY(EOMONTH(A1,0)),EOMONTH(A1,-1)+1)
EOMONTH(A1,0) is the last day of the month, so DAY of it is the
length, 31 for October and 30 for November, and
EOMONTH(A1,-1)+1 is the 1st whatever date you typed. The row spills across
on its own and leaves AF1 empty in a 30 day month. Anything typed in
C1:AF1 blocks the spill, and B1 shows #REF!.
Format B1:AF1 with Format, Number, Custom number format, ddd d, and
October reads Thu 1, Fri 2, Sat 3 while the cells stay real dates. Then View, Freeze,
1 row, and View, Freeze, 1 column.
A FILTER of the Staff sheet in A2:A25 is tempting. Do not. The codes
are typed and stay in their rows; names made by a formula do not. Add someone halfway
down the Staff list, or sort it, and every name below slides down a row while the
shifts stay put. From there down, every shift sits next to the wrong person, and the
totals still add up. Type the names, or paste them in as values.
Select B2:AF25, then Data, Data validation, Add rule, Dropdown (from a range),
pointed at Shifts!A2:A15. Under Advanced options, Reject the input refuses
anything that is not on the list, and that includes M/A. Show a
warning lets doubles in and typos with them, marked by a small red corner. If nobody
works doubles, reject. If they do, warn, and let the checks below catch the typos.
Select B1:AF25, then Format, Conditional formatting, Custom formula is:
=AND(B$1<>"",WEEKDAY(B$1,2)>5)
Light grey fill. WEEKDAY(date,2) numbers Monday as 1, so anything over 5
is the weekend. The first half matters: a blank cell is day zero, which Sheets dates
to Saturday 30 December 1899, so without it the empty columns of a short month turn
grey.
For colours, one rule per code on B2:AF25: Text is exactly, M, and
a fill, then the same for A and N. Keep them above the weekend rule, because where two
rules match one cell Sheets goes with the higher one, and a Saturday night shift
should look like a night shift. M/A matches none of them and stays plain, which is not
a bad way for doubles to stand out.
In the copy, 2. Build this month does this in one click: it draws the month grid with weekends shaded, a colour for each of your shifts and a dropdown of your shift codes in every cell. Two shifts in one cell, written as M/A, are allowed. It does not fill the rota in for you.
A tidy rota can still send someone home at 23:00 and back at 07:00. These are the seven checks worth running before anyone sees it, and how far a formula gets with each.
| Check | What it catches | With your own formulas |
|---|---|---|
| Unknown shift code | a code in the grid that is not on the Shifts sheet | one conditional format, below |
| Not on Staff sheet | a name in the grid with no row on the Staff sheet | one formula |
| Over weekly hours | more hours in a week than that person's limit | works inside the month |
| Too many days in a row | a run of working days longer than your limit | helper row, blind at month edges |
| Not enough rest | too short a gap between one shift ending and the next starting | a list of bad pairs, day before only |
| Double booked | a shift starting before the previous one has finished | the same list, the same limits |
| Overlapping shifts | two codes in one cell whose times run into each other | countable, not checkable |
The name check and the run of days are on the seven mistakes page, under names that do not line up and too many days in a row. Two are worth doing differently, so they are here.
Select B2:AF25, then Format, Conditional formatting, Custom formula is:
=AND(B2<>"",ISERROR(FIND("/",B2)),COUNTIF(INDIRECT("Shifts!A2:A15"),B2)=0)
Red fill, dragged to the top of the list. A code with a trailing space looks fine and
matches nothing, and this turns it red as you type. The INDIRECT is there
because a conditional format will not take a reference to another sheet written out
directly, only one handed over as text. The FIND skips cells with a
slash, so doubles are not checked here.
Measuring rest properly takes the date and time of every finish, which the mistakes page builds with four mirror grids. With three working codes there is a shorter way: only nine pairs can follow each other on consecutive days, and you can work them out once.
A finishes at 23:00 and M starts at 07:00, so A then M is 8 hours. N finishes at
07:00, so N then M is 0 hours and N then A is 8 hours. The other six are
16 hours or more, so against an 11 hour minimum three pairs are short. Type them from
Schedule!A32 down as A-M, N-M and N-A,
select C2:AF25, one column in from the first day, and add:
=COUNTIF($A$32:$A$40,B2&"-"&C2)>0Each cell joins yesterday's code to today's and looks the pair up, so Wednesday's M goes orange after Tuesday's A. Put the rule above the shift colours, or the colour wins and the warning never shows. That works. It is also a list you keep by hand: change a shift time and it is wrong until you redo it, and a fourth code turns nine pairs into sixteen. It compares a day only with the day before, cannot see inside a double, and never compares the 1st with the month before.
In the copy, 3. Check for problems runs all seven across the whole month. Findings go to a separate Issues sheet, one line per problem with the person, the date and what is wrong, and the check never writes to your grid. Five of the seven also give the cell. A week over its limit and a run of days belong to no single cell, so those two leave it blank.
Every limit on a rota is a number somebody chose. The copy starts at 11 hours of rest, 45 hours a week and 6 days in a row, and all three can be changed.
In your own sheet, give each limit one labelled cell and point the formulas at it. A 45 typed into a formula and dragged across the grid is 45 in 744 places. The pairs list breaks this rule because it has to, so write the 11 next to it where the next person will see it.
Keep two look-alike numbers apart. A weekly limit is where you want a warning; an overtime threshold is where pay changes. By default the copy flags anyone over 45 hours in a week and counts overtime after 40, and every one of those limits is a number you can change on the Settings sheet.
None of this is legal advice. The copy does not enforce employment law: the limits are the numbers you enter, so check your contracts and the rules where you work first. And a limit written as an average over several weeks is not a cap on every week. A check set to that number sees one week at a time, and it will flag weeks that are allowed.
A night shift belongs to the day it starts and runs 8 hours, not minus 16. After that, hours per person is a lookup, until somebody works a double.
The Shifts column already handles the night. An N on Monday 5 October 2026 counts 7.5 hours in Monday's column, although most of it is worked on Tuesday. That is the start-day rule, and the night shift hours page shows how it can decide whether you owe overtime.
On the Hours sheet, put this in B2 and drag it right to AF and down to row 25:
=IF(Schedule!B2="",0,SUMPRODUCT(IFERROR(VLOOKUP(TRIM(SPLIT(Schedule!B2,"/")),Shifts!$A$2:$D$15,4,FALSE),0)))
SPLIT breaks the cell at the slash, TRIM takes off stray
spaces and VLOOKUP finds each code's hours, so an M/A on Tuesday 6 October
is 7.5 plus 7.5, 15 hours. A single code splits into itself. The row formula on
the mistakes page gives
0 for the same cell, because M/A is not a code, and the unknown code rule above
skips it, so nothing turns red. A
person's month is then =SUM(Hours!B2:AF2), and weekly totals come off
this grid the way the weekly
overtime page builds them.
One blind spot survives. A typo inside a double, M/Q, counts the M and quietly drops
the Q, and the unknown code rule skips cells with a slash. Count the doubles with
=COUNTIF(Schedule!B2:AF25,"*/*") and read those cells by eye.
In the copy, a 23:00 to 07:00 shift is read as eight hours ending the next morning, not minus sixteen, and rest is measured from the real finishing time rather than by calendar day. 4. Totals gives normal, overtime and total hours per person, with overtime hours worked out week by week, after 40 hours by default, rather than by averaging the month. When a week runs across two months, the Totals sheet names that week instead of quietly under-counting overtime. It does not work that week out: the rest of that week is on another month's grid, and Totals does not read it.
Two shifts in one cell appear as separate lines on the timesheet. Timesheet for one person lists the date, day, shift code, shift, start, end, break and hours, then days worked, overtime hours and the total, with an employee and a manager signature line. It is built from the rota, so it shows what was planned: if a shift was swapped, change the grid before you print.
The obvious move, renaming this month's tab and starting a fresh one, is the one that quietly breaks every formula that reads the grid.
Renaming a tab rewrites every formula that points at it. Rename Schedule to Schedule 2026-10 and the Hours sheet and every formula that reads the grid follow it into the archive, and November is checked by nothing. Nothing errors. The numbers just stop moving.
Do it the other way round. At the end of October, right-click the Schedule tab, Duplicate, and rename the copy Schedule 2026-10. That is the archive, and nothing points at it. Duplicate Hours too and paste the copy over itself with Paste special, Values only, or it recalculates from November. The boundary week on the weekly overtime page reads last month from exactly that kind of copy. Then type a date in November into A1, select B2:AF25 and press Delete. The date row redraws to 30 days, and the dropdown and colours stay with the tab.
In the copy, building a new month never erases a grid that already has shifts in it; the old grid is renamed, for example to Schedule 2026-08, and kept. Load an example month on the menu loads a month with deliberate mistakes so you can watch the checker find them before you trust it with real names. It refuses to write over a month that already has shifts in it.
The free copy is the whole rota. The $39 version adds money and paperwork, and some things neither version does.
| What you get | Free copy | Full version, $39 once |
|---|---|---|
| Month grid, weekend shading, colours, dropdown | yes | yes |
| All seven checks, on an Issues sheet | yes | yes |
| Hours per person, overtime hours week by week | yes | yes |
| Timesheet for one person at a time | yes | yes |
| Every timesheet in one go | the first one | yes |
| Pay rates and labour cost, with overtime at the multiplier you set, on the Totals sheet | first 30 days from your first Totals | yes |
The free version has no limit on how many people you schedule, and the grid, checks, hours and single timesheets do not expire. The full version is $39, paid once; it is not a subscription. Refunds within 14 days, no questions asked. What the money buys is a licence code that switches the paid features on in the spreadsheet you already have. You get it by email after purchase and enter it with ShiftSheet ▸ Enter licence code (Extensions ▸ ShiftSheet if you installed from the Marketplace). There is no second file and nothing to move across, and the code is tied to your Google account. If the workbook already has its own tab called Totals, Issues or Timesheet, ShiftSheet writes its report to a new tab instead of clearing yours.
No shift swaps, time-off requests, staff app or notifications, and no Google Calendar sync. One team in one spreadsheet, so no multiple locations. A monthly grid only, with no weekly rota view. It does not fill the rota in for you, it has no check for too few people on a shift, and it is not payroll software: it does not calculate tax or pay anyone. If staff swap shifts from their phones, that is a job for a scheduling app, not a spreadsheet.
The missing check is one you can add to your own grid. Put M, A and N in Schedule!A27:A29 and the fewest people each needs in AG27:AG29, the column after the 31st. Then this goes in B27, dragged right to AF and down to row 29:
=COUNTIF(B$2:B$25,$A27)+COUNTIF(B$2:B$25,$A27&"/*")+COUNTIF(B$2:B$25,"*/"&$A27)That counts people on the code alone plus doubles that start or end with it. Then conditional format B27:AF29 with:
=AND(B$1<>"",B27<$AG27)Red fill. A night with one person when you need two goes red, and the empty columns of a short month stay quiet. It counts people rostered on a code, not who is in the building at 14:00, which is a question about times, and a cell with three codes counts only its first and last.
If you would rather not build all of this and keep it in step by hand, this is the part ShiftSheet handles in the same spreadsheet.
The month grid, the seven checks and hours per person with weekly overtime hours arrive built, with none of the formulas above to maintain; the coverage check does not. One difference from the build is worth flagging: its dropdown does not block other entries. M/A goes in without a fight, and anything else typed in is flagged as an unknown shift code when you run the check, not stopped as you type. It is free for a team of any size, with no sign-up with us, and its script declares one permission: to read and write the one spreadsheet you open it in. Installed from the Marketplace, Google's install screen lists two more, your email address and your basic profile info. The add-on makes no network calls at all. The free version shows pay rates and labour cost for 30 days from your first Totals and prints every timesheet in one go once. The $39 version, paid once, keeps pay rates and labour cost, with overtime at the multiplier you set, on after that and prints every timesheet in one go every time.
Also on the Google Workspace Marketplace. Either route runs the same code. The other guides here cover the seven things that go wrong in a shift schedule, why night shift hours come out negative and weekly overtime on a monthly rota.