Most articles about staff scheduling in Google Sheets hand you a template and then tell you to buy an app. This one does neither. It goes through the seven things that actually go wrong in a work schedule built on a grid, gives you working formulas for the four you can catch yourself, and is straight with you about the three where a formula runs out of road.
Every formula below refers to real cell ranges, so you need to know which ranges. Change them to match yours, but change them everywhere.
A2:A25 holds one name per row. Nothing else on this sheet matters for the checks, though a weekly hour limit per person is handy in column D.
A2:A15 is the shift code, B the start time, C the end time.
Times, not text. If 07:00 sits on the left of the cell it is text
and the arithmetic below will fail.
A2:A25 repeats the names, B1:AF1 holds the 31 dates as real dates, B2:AF25 is the grid you type codes into. One code per cell, for now.
Three extra sheets the formulas write into. Nothing is typed on them by hand. Keeping the working out off the roster is the single best decision in this whole setup.
Nothing else works without this. Put it in Shifts!D2.
=ARRAYFORMULA(IF(A2:A15="","",MOD(C2:C15-B2:B15,1)*24))
The MOD is the whole point. A night shift from 23:00 to 07:00 gives
C-B = -16 hours, which is nonsense, and MOD turns it into
8. Subtracting times directly is the most common single error in a hand-built
shift planner, and it under-reports every night shift you run. There is a page of
its own on this: night
shift hours in Google Sheets, covering unpaid breaks, which day a night
shift belongs to, and times that are secretly text.
One thing to know: a shift whose start and end are identical comes out as 0 rather than 24. If you genuinely run 24-hour shifts, split them.
A roster drifts. Someone leaves, someone is hired, a name gets typed as Kate on one sheet and Katie on the other, and from then on that person has no hour limit and no pay rate applied to them.
Put these on the Problems sheet. Column B, three rows.
=IFERROR(TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Schedule!A2:A25,
Schedule!A2:A25<>"",COUNTIF(Staff!$A$2:$A$25,Schedule!A2:A25)=0))),"none")=IFERROR(TEXTJOIN(", ",TRUE,FILTER(Staff!A2:A25,
Staff!A2:A25<>"",COUNTIF(Schedule!$A$2:$A$25,Staff!A2:A25)=0)),"none")=IFERROR(TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Schedule!A2:A25,
Schedule!A2:A25<>"",COUNTIF(Schedule!$A$2:$A$25,Schedule!A2:A25)>1))),"none")The second one is worth more than it looks. Every employee scheduling tool tells you who is double booked. Almost none tell you who you forgot to put on the rota at all, which is the mistake you find out about at 07:00 when nobody opens up.
All three depend on COUNTIF matching text exactly, which means a
trailing space defeats them. Wrap the ranges in TRIM if your names
come from a form.
You type N for night. One Tuesday you type n, or
Nn, or N with a space. The cell looks fine. It is worth
zero hours in every total you calculate from it.
Catch it where you can see it. Select Schedule!B2:AF25, then Format, Conditional formatting, Custom formula is:
=AND(B2<>"",COUNTIF(Shifts!$A$2:$A$15,B2)=0)Set it to a red fill. Every cell holding a code that is not in your shift library goes red the moment you type it. It takes a minute to set up and catches more than anything else here.
Better than catching it is preventing it. Select the same range, Data, Data
validation, Dropdown from a range, and point it at Shifts!A2:A15. Set it to reject invalid input, and the grid only accepts codes that exist. Shift management gets a lot quieter when
typing is no longer possible.
This is the one that costs money. Overtime is almost always defined per week, and a month grid tempts you into adding up the month. Fifty hours one week and thirty the next averages to forty and hides ten hours of overtime.
On the Hours sheet, put this in B2 and drag it down to row 25. It turns the whole grid into hours.
=ARRAYFORMULA(IF(Schedule!B2:AF2="",0,
IFERROR(VLOOKUP(Schedule!B2:AF2,Shifts!$A$2:$D$15,4,FALSE),0)))
One formula per row, 31 columns wide. The month total for a person is then just
=SUM(B2:AF2), and you have a second use for it: a cell with something
typed in it that produced 0 hours is a typo. On the Problems sheet:
=SUMPRODUCT((Schedule!$B$2:$AF$25<>"")*(Hours!$B$2:$AF$25=0))Now the weekly part. Put your week start dates in Hours!AH1 going right, one Monday per column, then this in AH2 and drag it right and down.
=SUMPRODUCT((Schedule!$B$1:$AF$1>=AH$1)*(Schedule!$B$1:$AF$1<AH$1+7)*$B2:$AF2)
That is hours per person per week, counted by actual date rather than by column
position. Put your limit in a spare cell, say AO1 = 48, and
conditional format AH2:AM25 with =AH2>$AO$1.
Here is the part the template articles leave out. The first and last weeks of any month are cut in half. Someone who worked 20 hours on the 29th, 30th and 31st of last month and 32 hours on the 1st and 2nd of this one is over 48 for that week, and no formula living on this month's sheet can see it. A monthly grid and a weekly limit disagree about where the week starts, and that disagreement is structural, not a bug you can formula your way out of. There is a page of its own on that week: weekly overtime on a monthly rota, with the three ways round it and what each one costs.
Eleven days without a day off does not look like anything on a grid. Each cell is innocent. It is the run that is the problem.
This one needs a helper sheet, and the reason is worth understanding. Counting a run means each day's answer depends on yesterday's answer, and an array formula cannot refer to its own output. So on the Runs sheet, B2 is:
=IF(Schedule!B2="",0,1)and C2 is:
=IF(Schedule!C2="",0,B2+1)
Drag C2 right to AF2, then select B2:AF2 and drag the whole thing down to row 25.
The longest run for a person is =MAX(B2:AF2). Flag anything over your
limit with =IF(MAX(B2:AF2)>6,"check","").
There is a single-cell version of this using MMULT. It works and it
is unreadable, and the next person to inherit your spreadsheet will not be able to
change it. A visible helper row is the better engineering decision even though it
looks less clever.
Same month boundary problem as the weekly limit: a run that started on the 28th of last month reads as starting on the 1st.
Two of the seven I would not attempt in a spreadsheet, and one I would attempt only if the grid stays simple. Being clear about which is which saves you an afternoon.
This is the check that matters most for the people on the rota and it is the one a spreadsheet handles worst. To know the gap you need the exact moment the previous shift ended, as a date and a time together, and for a night shift that moment is on the following calendar day. So you need a second mirror grid for start times:
=ARRAYFORMULA(IF(Schedule!B2:AF2="","",
IFERROR(VLOOKUP(Schedule!B2:AF2,Shifts!$A$2:$D$15,2,FALSE),"")))a third for end moments, built from the date, the start and the length:
=IF(Schedule!B2="","",Schedule!B$1+Starts!B2+Hours!B2/24)and a fourth for the gap itself, in hours:
=IF(OR(Schedule!C2="",Schedule!B2=""),"",(Schedule!C$1+Starts!C2-Ends!B2)*24)That works. It is also four sheets of scaffolding to check one rule, and it still only compares a day against the day immediately before it. A gap that spans a day off is not measured, which is usually harmless and occasionally not, when the earlier shift ran past midnight. Put two codes in one cell and the whole thing returns the wrong shift's times without telling you.
Writing M/A in a cell for a double is a reasonable habit. Checking
whether those two shifts collide is not reasonable to do with formulas.
SPLIT gives you a variable number of parts, so a formula written for
two codes silently ignores the third, and you have to compare every pair of start
and end times against every other pair. You can at least count them. On the
Problems sheet:
=COUNTIF(Schedule!B2:AF25,"*/*")That tells you how many cells hold more than one shift. Whether they overlap is a different question and that is where I would stop.
A schedule is a plan. Attendance is what happened. Keep them on separate sheets, because the moment you start editing last week's rota to record who actually turned up, you have destroyed the only record of what you asked people to do. Copy the month, rename the copy, and let the plan stand. The timesheets you hand to payroll come off the attendance copy. The plan is what you promised, and it is worth being able to look back at.
Four checks that work properly and take about half an hour to set up. Three that are honest work.
| Check | With formulas |
|---|---|
| Names not on the staff list | one formula |
| Unknown shift code | conditional format, plus a dropdown |
| Hours per person | one mirror grid |
| Over the weekly limit | works inside the month |
| Too many days in a row | helper row, blind at month edges |
| Not enough rest | four mirror grids, breaks on doubles |
| Overlapping shifts in one cell | detectable, not checkable |
If you run one location and a stable team, that is genuinely enough, and the schedules you build this way stay yours in a file you own. The formulas start to hurt when the team grows, when doubles become normal, or when a run of days crosses from one month into the next.
All seven of these are what ShiftSheet does, in one pass over the month, in the same spreadsheet.
Findings go to their own Issues sheet with the person and the date on each line, and your grid is never edited. It is free for a team of any size, with no account or 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. The add-on makes no network calls at all. The $39 version adds the money side: pay rates turned into what the month cost, and every timesheet printed in one go instead of one at a time.
Also on the Google Workspace Marketplace.
Either route runs the same code. If you would rather keep your own formulas,
keep them, and take the MOD one at the top with you.