A monthly roster is a grid of names by 31 days, and it is only half the job. At the end of the month it has to become hours per person, overtime by the week and a timesheet each person signs. This page builds all of it from formulas you can read, and is straight about the two places a formula roster runs out of road: a double shift, and the week that runs into next month.
Roster means two things, and the search results mix them up. This page is about the one with shifts in it.
A team roster lists members: a name, a position, a phone number. Sports clubs keep them, and some of what comes back when you search for a roster template is exactly that. A staff roster is a plan of who works which shift on which day. Australia calls it a roster, Britain a rota, American sites a schedule. Same grid.
The template here is the shift kind, laid out as on the seven mistakes page, with three sheets added so it runs from the grid to a page somebody signs. Change the ranges to match yours, but change them everywhere.
Staff!A2:A25 names, D weekly hour limit, E hourly rate. Shifts!A2:A15 code, B start, C end, D paid hours (a formula, never typed), E unpaid break in minutes, F the shift's name.
A2:A25 names, B1:AF1 the dates of the month as real dates,
B2:AF25 the grid, one code per cell or two written as
M/A.
The mirror grid: B2:AF25 holds hours where the Schedule holds codes. Row 1 copies the dates, rows 26 and 27 carry a week key and a fortnight key. Nothing on it is typed.
Totals has one row per person, the weeks in J:O and four settings in H1:H4. Timesheet shows one person at a time, whoever is picked in B1.
Two short lists that everything else looks up. Get them right once and the grid is only typing.
One row per kind of shift. Three are enough to follow this page: M
for 07:00 to 15:00, A for 15:00 to 23:00 and N for 23:00
to 07:00, each with a 30 minute unpaid break in column E and a name in F. Paid
hours go in Shifts!D2:
=ARRAYFORMULA(IF(A2:A15="","",MOD(C2:C15-B2:B15,1)*24-IF(E2:E15="",0,E2:E15)/60))
All three come out at 7.5 hours. Subtract 23:00 from 07:00 directly and
you get minus 16. MOD turns that into 8 before the break comes off,
and the night shift hours
page walks through why. Breaks go in whole minutes, thirty and not
0:30.
Leave days off empty rather than typing OFF. A code with no times is
worth 0 hours, and the typo counter on the mistakes page will count every one. On
the Staff sheet, column D is each person's weekly hour limit, because a part timer
on 20 hours and a full timer on 38 cannot share one number. Column E, the rate,
only matters if you want the month's cost.
Type one date and the month lays itself out. The real work is the doubles, because the usual hours formula cannot read them.
In Schedule!B1 type =DATE(2026,10,1) for October 2026, which
sidesteps whether 1/10 means January or October in your locale. Then this in
C1, dragged right to AF1:
=IF(B1="","",IF(MONTH(B1+1)=MONTH($B$1),B1+1,""))
Each cell adds a day until the month changes, then goes blank. A 30 day month
leaves AF1 empty. Format, Number, Custom number format, ddd d
shows Thu 1, Fri 2 and so on. For weekends, select B1:AF25, then Format,
Conditional formatting, Custom formula is:
=AND(B$1<>"",WEEKDAY(B$1,2)>5)
Select B2:AF25, then Data, Data validation, Dropdown from a range, pointed
at Shifts!A2:A15, and under Advanced options set it to show a
warning. Rejecting is tidier until somebody works a double. M/A is
not on the list, so a grid that rejects will not take it. Typos then need catching
another way, with a red fill on the same range, Format, Conditional formatting,
Custom formula is:
=AND(B2<>"",SUMPRODUCT(--(COUNTIF(INDIRECT("Shifts!A2:A15"),TRIM(SPLIT(B2,"/")))=0))>0)
SPLIT breaks M/A into its codes, and the cell goes red
if any of them is missing from the Shifts sheet. The INDIRECT is not
decoration. Conditional formatting in Google Sheets can only refer to its own
sheet, and Google's help says to reach another one through
INDIRECT.
The hours grid on the mistakes page looks the whole cell up, so M/A
finds nothing and comes back as 0. This version splits first. In
Hours!B2, dragged right to AF2 and down to row 25:
=IF(Schedule!B2="",0,ARRAYFORMULA(SUM(IFERROR(VLOOKUP(TRIM(SPLIT(Schedule!B2,"/")),Shifts!$A$2:$D$15,4,FALSE),0))))
Kate works M/A on Saturday 10 October 2026: 7.5 plus 7.5, so
15 hours from one cell. A night shift goes in the column of the day it
starts, and the night shift
hours page shows how that choice can decide whether overtime is owed.
An unknown code inside a double counts as nothing. M/X comes back as
7.5, which is why the red rule checks every part of the cell and not the cell as
a whole.
And the two shifts are added without asking whether they overlap.
M/L, with L starting at 14:00, counts both in full.
=COUNTIF(Schedule!B2:AF25,"*/*") tells you how many doubles there
are. Checking them is done by eye.
One row per person: days, normal hours, overtime and the total. Overtime is counted by the week, because that is how it is owed.
Four settings sit in Totals!H1:H4, labelled in column G. H1 is the weekly hours after which overtime starts, 40 here as an example, not advice. H2 is the week type, 2 for Monday weeks or 1 for Sunday, which a contract decides and not you, as the weekly overtime page explains. H3 is any date a pay fortnight starts, H4 the overtime multiplier.
The Hours sheet copies the dates into B1:
=ARRAYFORMULA(IF(Schedule!B1:AF1="","",Schedule!B1:AF1))and gets a week key in B26, dragged right to AF26:
=IF(B1="","",B1-WEEKDAY(B1,Totals!$H$2)+1)
On Totals, J1 is the first week start,
=Schedule!B1-WEEKDAY(Schedule!B1,$H$2)+1, and K1 is
=J1+7, dragged to O1. Six columns, because a 31 day month can
touch six weeks. Hours per week go in J2, dragged to O25:
=SUMIF(Hours!$B$26:$AF$26,J$1,Hours!$B2:$AF2)
A2 is =Schedule!A2, dragged down, so each row is the same
person on every sheet. B2, days worked, is
=COUNTIF(Hours!B2:AF2,">0"), which counts a double once.
E2, the total, is =SUM(Hours!B2:AF2). D2 is the
overtime:
=SUMPRODUCT((J2:O2>$H$1)*(J2:O2-$H$1))
and C2, normal hours, is =E2-D2. Kate's October runs 22.5
hours in the week that began on 28 September, then 52.5, 30, 30 and 22.5. That is
157.5 hours over 20 days. Averaged, it is under 36 hours a week and no
overtime at all. By the week, the second one is 12.5 hours over, so Totals shows
145 normal and 12.5 overtime. A quiet week does not buy back a busy
one.
October 2026 starts on a Thursday, so its first week began on Monday 28 September. If Kate also worked three mornings on 28, 29 and 30 September, each month's sheet sees 22.5 hours of that week. The real week is 45, five hours of overtime that neither sheet shows. October's sheet cannot see September, so the honest move is to name the weeks affected. In Totals!J26, dragged to O26:
=IF(J$1>MAX(Hours!$B$1:$AF$1),"",IF(OR(J$1<MIN(Hours!$B$1:$AF$1),J$1+6>MAX(Hours!$B$1:$AF$1)),"partial","whole"))and one line in A27 for whoever pays the overtime:
=ARRAYFORMULA("Check against the next or last month: "&TEXTJOIN(", ",TRUE,IF(J26:O26="partial",TEXT(J1:O1,"d mmm"),"")))For October it names 28 Sep and 26 Oct. The overtime shown for a partial week is a floor, not the answer, and the week that belongs to two months sets out three ways to fix it properly.
With a rate in Staff!E and 1.5 in H4, in Totals!F2:
=LET(rate,IFERROR(VLOOKUP($A2,Staff!$A$2:$E$25,5,FALSE),0),C2*rate+D2*rate*$H$4)Normal hours already leave the overtime out, so the overtime is priced at the full 1.5 times and not at an extra half. At $30 an hour Kate costs 145 times 30 plus 12.5 times 45, which is $4,912.50. Starting from all 157.5 hours and adding half the rate for the 12.5, as the weekly overtime page does, gives the same figure. A name missing from the Staff sheet costs nothing, silently.
One person, one month, one page with two signatures. It is the roster printed for one person, which is its strength and its limit.
On a sheet called Timesheet, B1 is a dropdown of names from
Schedule!A2:A25 and B2 is =Schedule!B1, formatted
as mmmm yyyy. Row 4 holds the headings: Date, Day, Code, Shift, Start,
End, Break (min), Hours. The days the chosen person works go in A5:
=IFERROR(TRANSPOSE(FILTER(Schedule!$B$1:$AF$1,INDEX(Hours!$B$2:$AF$25,MATCH($B$1,Schedule!$A$2:$A$25,0),0)>0)),"")
MATCH finds the person's row, INDEX takes that row of
hours, FILTER keeps the dates with hours on them and
TRANSPOSE stands them in a column. B5 is
=IF(A5="","",TEXT(A5,"ddd")). C5 fetches the code:
=IF(A5="","",INDEX(Schedule!$B$2:$AF$25,MATCH($B$1,Schedule!$A$2:$A$25,0),MATCH(A5,Schedule!$B$1:$AF$1,0)))Shift, Start, End and Break are one lookup each against the Shifts sheet, and only the column number changes: 6, 2, 3 and 5. Start, in E5:
=IF(C5="","",IFERROR(VLOOKUP(C5,Shifts!$A$2:$F$15,2,FALSE),""))
H5 is the C5 formula pointed at Hours!$B$2:$AF$25, so a double
arrives already added up. Drag B5:H5 down to row 35. In row 37, Days worked is
=COUNT(A5:A35), TOTAL is =SUM(H5:H35), and Overtime
hours comes from the Totals sheet:
=IFERROR(INDEX(Totals!$D$2:$D$25,MATCH($B$1,Totals!$A$2:$A$25,0)),0)Two rows lower go Employee signature and Manager signature, each over a cell with a bottom border. Select A1:H40, then File, Print, Selected cells, Fit to width.
Kate's October prints as 20 lines: Days worked 20, Overtime hours
12.5, TOTAL 157.5. Saturday 10 October reads M/A, 15
hours, with no start or end, because one line cannot hold two start times. A day
whose only code is unknown has no hours, so it drops off without a word.
Every number here comes from the roster. If someone swapped a Tuesday or went home at 13:00, the timesheet still says 7.5. It records the plan, and the two signatures are both sides saying it matches what happened. So correct the hours before anyone signs, and do it on a copy: File, Make a copy, fix the copy, print from that. The mistakes page makes the case for leaving the plan alone.
The grid is a calendar month. Pay periods are not, and any period that crosses the 1st is split across two files.
Weekly pay is already done: Totals!J2:O25 are the pay weeks, and row 26 names the partial ones. For fortnights, put any date a pay fortnight starts in Totals!H3, say Monday 21 September 2026, then in Hours!B27, dragged right to AF27:
=IF(B1="","",B1-MOD(B1-Totals!$H$3,14))
MOD says how many days into its fortnight a date sits, and taking
that off lands on the start. It works for dates before the one in H3 too, since
MOD with a positive divisor never goes negative. On Totals, Q1
is =Schedule!B1-MOD(Schedule!B1-$H$3,14), R1 is
=Q1+14, dragged to T1, and Q2, dragged to T25,
is:
=SUMIF(Hours!$B$27:$AF$27,Q$1,Hours!$B2:$AF2)Four columns, because a month that starts on the last day of a fortnight touches four. October 2026 touches three, 21 September to 4 October, 5 to 18 October and 19 October to 1 November, and only the middle one is whole. Kate's come to 22.5, 82.5 and 52.5. Two of those are only part of the answer.
Every month splits at least one fortnight, bar a 28 day February that starts on one, and most split two. No formula changes that. Rostering in 28 day periods does, at the cost of the calendar month. And if your overtime line is drawn per fortnight rather than per week, compare Q2:T25 with it and leave the weekly overtime in column D out, because the two definitions disagree about the same hours.
A mistake found on the roster costs a retype. Found on a signed timesheet, it costs two signatures.
The seven mistakes page has the formulas. In brief, in the order worth running them before you print.
Names. A name in the grid but not on the Staff sheet, on the Staff sheet but never rostered, or on two rows. Here a name typed two ways also splits one person across two timesheets.
Codes. The red rule above, doubles included. It matters twice here, because a day with only an unknown code on it vanishes from the timesheet.
Weekly limits. Each person's limit goes in Totals!I2, dragged down to I25. The lookup on the weekly overtime page falls back to a default in M2, and column M here holds a week of hours, so this version leaves a missing limit blank instead:
=LET(lim,IFERROR(VLOOKUP($A2,Staff!$A$2:$D$25,4,FALSE),0),IF(lim=0,"",lim))
Then colour J2:O25 with =AND($I2<>"",J2>$I2). A limit
is the most someone should work. H1 is where the higher rate starts. Different
numbers.
Days in a row, rest, overlapping doubles. The three where formulas run out of road: a helper row that is blind at the edges of the month, four mirror grids, and a count you then check by eye.
Everything above works, and it is six sheets of formulas the next person has to trust. Three things it does not do, and one it should not try to.
A double is one line on the timesheet. Two lines with their own start and
end can be built from FLATTEN and two SPLITs, and nobody
who inherits the file will be able to change it. That is where I would stop.
The boundary week is named, not fixed. Getting its real number means bringing the other month's hours across. The weekly overtime page puts that at about ten minutes to set up, and it leaves a dependency between two files.
It is not payroll. No tax, no leave accrual, no weekend or public holiday penalty rates, and one overtime multiplier for everybody. Overtime is defined differently by country, award and contract, and the 1.5 in H4 is an example, not advice.
It should not become the attendance record. The roster is the plan. Edit it to record who turned up and the plan is gone, which is what the copy is for.
If you would rather not maintain six sheets of formulas, this is the part ShiftSheet handles in the same spreadsheet.
One click 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: they appear as separate lines on the
timesheet, and the checker tells you if they overlap. It runs seven checks across
the whole month, and findings go to a separate Issues sheet, one line per problem
with the person, the date and what is wrong.
The Totals sheet gives each person Days, Normal h, Overtime h and Total h, with overtime hours worked out week by week, after 40 hours by default. It has the same blind spot as this page: when a week runs across two months, the Totals sheet names that week instead of quietly under-counting overtime, and it does not fetch the other month's days. Each timesheet 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 made from the roster, so it shows planned hours. There is no clocking in, and the signatures go on paper.
| What you get | Free copy | Full version |
|---|---|---|
| Month grid, seven checks, hours and weekly overtime per person | yes | yes |
| Printable timesheet with two signature lines | one person at a time, plus every timesheet in one go once | every timesheet in one go |
| Pay rates and labour cost, with overtime at the multiplier you set | first 30 days from your first Totals | overtime at 1.5 times the rate by default, editable |
| Price | free, no limit on how many people you schedule; the grid, checks, hours and single timesheets do not expire | $39, paid once, not a subscription, refunds within 14 days |
It is not payroll software: it does not calculate tax or pay anyone, and there is one overtime multiplier for everyone. Paying unlocks the copy or Marketplace install you already use: you get a licence code 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. A formula roster built from this page does not move into the add-on by itself: the add-on's sheets order their columns differently and start the names on row 4, so type the names and shift times into its Staff and Shifts sheets and paste the codes into the grid it builds. 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.