How to make an employee schedule in Google Sheets, a whole month at a time

Why a month, not a week

A week is easier to draw. A month is what you pay, print and pin up, so it is the shape worth building once.

Say you pay monthly. At the end of October 2026 the questions are what the month cost and who worked what. On a month grid that is one tab with one row per person. On weekly tabs it is five. The month starts on a Thursday, so its first week began on Monday 28 September, and its last runs from Monday 26 October to Sunday 1 November. Only three of the five are wholly October. Totalling the month means adding five tabs together and taking out three September days and one November day, by hand, every month.

A month also prints as one landscape page with every day on it. And it is the grid the other guides on this site assume, so every formula they add works on what you build here without a single range being changed.

It is not free. Overtime is counted by the week, and a month usually cuts a week in two at each end. That is one week a month, and the page on the week that belongs to two months has three honest ways to handle it. Everything else is easier on a month.

Two lists before the grid

Same layout as the seven mistakes page, so every formula on the other guides here works on the grid you are about to build. Change the ranges to match yours, but change them everywhere.

Lists first, because the grid reads from both. The dropdown offers what is on Shifts, the name column copies what is on Staff, and every hours formula on the other pages looks a code up on Shifts to find out how long it is. A code or a name that exists only in the grid is worth nothing to any of them.

Three codes are enough to start: M from 07:00 to 15:00, A from 15:00 to 23:00 and N from 23:00 to 07:00, each with a 30 minute unpaid break, which makes 7.5 paid hours each. The length column is one formula, and it lives on the mistakes page rather than here, because the obvious subtraction gets N wrong: 23:00 to 07:00 comes out as minus 16 hours. The version that also takes the break off is on the night shift hours page.

The date row that fits any month

One cell you type and one formula that does the rest. October gets 31 columns, February 28, and nothing needs editing in between.

In Schedule!A1, put any date in the month you are planning, written as a formula rather than typed as 1/10/2026:

=DATE(2026,10,1)

A typed date is read through the sheet's locale, under File, Settings, and 1/10/2026 is 1 October in a UK sheet and 10 January in a US one. DATE takes the year, the month and the day in that order everywhere. Then Format, Number, Custom number format, and type mmmm yyyy. The corner of the grid now reads October 2026, and so will the printout.

The whole date row is one formula in B1:

=SEQUENCE(1,DAY(EOMONTH(A1,0)),EOMONTH(A1,-1)+1)

Walk it through for October. EOMONTH(A1,0) is the last day of the month, 31 October, and DAY of that is 31, the number of columns. EOMONTH(A1,-1) is the last day of September, and one day after it is 1 October, the start. SEQUENCE counts up by one from there, one row deep and 31 columns wide, and fills B1:AF1 by itself. Starting from the end of last month rather than from A1 means A1 can hold the 19th as happily as the 1st. For February 2027 the same formula stops at AC1 and leaves the last three columns empty, and February 2028 gets its 29th without being told.

If the row shows plain numbers such as 46296, those are the dates underneath, and the next step formats them. Check the row rather than trusting it: =COUNT(B1:AF1) counts real dates and should say 31 for October, 30 for November and 28 for February 2027.

The day name without an extra row

Select B1:AF1, Format, Number, Custom number format, and type ddd d. The cells read Thu 1, Fri 2, Sat 3, and they still hold dates, which is what every formula downstream compares against.

The other way is a second header row of day names:

=ARRAYFORMULA(IF(B1:AF1="","",TEXT(B1:AF1,"ddd")))

The IF is not decoration. An empty cell is 0 to Sheets, day 0 is Saturday 30 December 1899, and without it February would end in three columns labelled Sat. The row is the real cost, though. It pushes the first name down to row 3, and every formula on the other guides expects it in row 2. A number format gives you the same word without moving anything, so that is what this page uses.

B1 has two limits. It owns B1:AF1, so type something in C1 and B1 shows #REF!, with Array result was not expanded because it would overwrite data in C1. That one you will see. The other you will not: change A1 on a grid that already has shifts in it and the dates move while the codes stay put, so every October shift is suddenly sitting under a November date. The section on starting the next month has the order that avoids it.

Names down the side, and a list that must not move

Pull the names from Staff so the grid and the list cannot disagree. Then decide when the list is allowed to change, because the formula will not decide for you.

In A2:

=FILTER(Staff!A2:A25,Staff!A2:A25<>"")

It copies every name on Staff in the same order and skips blank rows, so a list with a gap in it still makes a grid without one. Then View, Freeze, 1 row, and View, Freeze, 1 column. Scroll across to the 28th and the dates and the names stay where you can see them.

Two limits worth knowing before you trust it

The names move and the codes do not. FILTER recalculates every time Staff changes, but the codes in the grid are typed values sitting in fixed cells. Say Staff has eight names and Ben, the third, leaves on 12 October. Delete his row and the five names below him each move up a row, into the shifts that were typed for the person above. Nothing turns red. Every row still has a name and a month of codes. They are just not each other's any more. Sorting Staff into alphabetical order does the same thing to everybody at once.

So pick one of two rules and write it in a cell on the Staff sheet where the next person will see it. Either no row is deleted, inserted or sorted while a month is live: leavers stay until the month ends, and new starters go on the first empty row at the bottom, where they move nobody. Or, once the codes are in, select A2:A25, copy it, then Edit, Paste special, Values only, and the names stop following the list. The formula is for building the column, not for keeping it.

The second limit is louder. Type anything in a cell below A2 that the list needs, and FILTER refuses to fill it, with the same Array result was not expanded message and the address of the cell you typed in. An error is the good outcome here, because you can see it. A name typed straight into the grid that is not on Staff is the first mistake on the seven mistakes page, with a formula that lists every one.

A dropdown of your shift codes

The dropdown saves typing. Whether it also stops a typo depends on one setting, and it sits under Advanced options.

Select B2:AF25, then Data, Data validation, Add rule. Under Criteria choose Dropdown (from a range) and give it Shifts!A2:A15. Every cell in the grid now offers M, A and N, and a code added to the Shifts list later turns up in the dropdown without the rule being touched.

Show a warning, or reject the input

Open Advanced options and look at If the data is invalid. Show a warning lets the typo in and marks the cell with a small red corner. You type NN, or N with a space after it, the cell looks fine from across the room, and it is worth zero hours in every total that looks it up. Reject the input refuses anything that is not on the list, and the person typing gets an error instead. For a rota, reject. It has one cost if you roster doubles: M/A is not on the list either, so it is refused too. Add each pair you use to Shifts as a code of its own, with its own start and end, or choose Show a warning and accept that every double gets marked as invalid.

One limit no setting fixes. The rule can only refuse a value as it is typed. Rename N to NT on the Shifts sheet halfway through a month and every N already in the grid stays an N, a code that no longer exists and is worth nothing. The red fill on the seven mistakes page catches that and the typo alike, whichever setting you chose. It takes a minute to set up, and it has to be the first rule in the list, for a reason the next section explains.

Weekends shaded, and a colour for each shift

Two kinds of conditional formatting on the same cells. The order of the rules decides which one shows on a Saturday.

Weekends

Select B1:AF25, then Format, Conditional formatting, and under Format cells if choose Custom formula is:

=AND(B$1<>"",WEEKDAY(B$1,2)>5)

Give it a light grey fill. WEEKDAY with a 2 numbers the days from Monday as 1 to Sunday as 7, so anything above 5 is a Saturday or a Sunday. The dollar sign in front of the 1 pins every row to the date row, so the whole column goes grey, not only the header. The AND is there for the same reason as the IF above: an empty column at the end of a short month reads as 0, 0 is a Saturday, and without it February gets three grey columns that are not days at all. October 2026 should show nine grey columns, five Saturdays and four Sundays.

One colour per shift

Add a rule to B2:AF25 for each code: Format cells if, Text is exactly, M, and a pale fill. Then the same for A and for N, pale yellow, pale blue and pale purple, say. Pale, because the code has to stay readable on paper.

When two rules match one cell, the one higher in the list wins, so drag the weekend rule to the bottom. A Saturday with an M on it then shows in M's colour inside a grey column, and an empty Saturday stays grey. If you added the red fill from the seven mistakes page, drag it to the top, above everything. Below the shift colours, a renamed N still matches N's own rule and stays purple. Below the weekend rule, a typo on a Saturday stays grey. Either way nothing turns red.

Every code is its own rule. Eight codes means eight rules, and a code you add to Shifts in March stays white until somebody adds its rule too. A double written as M/A matches none of them and stays white on a weekday, which at least makes doubles stand out. And colour is not a check. A purple N on Saturday 10 October and a yellow M on Sunday 11 October look like a tidy weekend, and they leave no rest at all: the night shift ends at 07:00 on Sunday, the moment the morning shift starts.

Days worked, people per day, and where the hours come from

Counting days is one formula on this sheet. Counting hours properly takes three pages, and they are linked here rather than squeezed in.

Type Days in AG1, then in AG2, dragged down to row 25:

=IF(A2="","",COUNTIF(B2:AF2,"?*"))

The ?* means at least one character of text, so every cell with a code in it counts once and a blank day does not. M/A counts as one day, which is right: it is one day with two shifts in it. The IF keeps rows without a name blank instead of showing a column of zeros.

It counts anything typed. OFF, HOL and SICK are all days worked as far as this formula knows. Leave days off blank, or if you mark holiday in the grid, take it back off:

=IF(A2="","",COUNTIF(B2:AF2,"?*")-COUNTIF(B2:AF2,"HOL"))

The same idea turned on its side tells you how many people are on each day. In B26, dragged right to AF26:

=IF(B$1="","",COUNTIF(B2:B25,"?*"))

A Saturday with one person on it shows up as a 1 in a row of fours. It counts people with a code, not whether the right shifts are covered, and HOL counts here as well.

Hours take three pages, in this order

Hours need each code turned into a length first. The mirror grid on the seven mistakes page does that with one lookup per row, the month total on that sheet becomes =SUM(B2:AF2), and a cell with something typed in it that produced 0 hours is a typo. If you run nights, the night shift hours page comes next, because 23:00 to 07:00 is 8 hours only once the subtraction is fixed. Then weekly overtime inside the month, because a month total cannot answer a weekly question: 50 hours one week and 30 the next average to 40 and hide 10 hours of overtime against a 40 hour week.

Printing a month, and starting the next without losing this one

One landscape page. Then a copy for the record before the month cell changes, never after.

Printing

Select A1:AG26, then File, Print, and set Print to Selected cells, Page orientation to Landscape and Scale to Fit to width. Tick Show gridlines under Formatting, because a page of pale fills with no lines between them is hard to read across. A1 prints the month name in the top corner, which is the reason it was given a format.

Starting the next month

First, right-click the Schedule tab, Duplicate, and rename the copy after the month it holds: Schedule 2026-10. That copy is the record. If its names are still a formula, select A2:A25 on the copy, copy it, and Paste special, Values only, or next month's changes to Staff will reshuffle this month's rows the way the names section describes.

Then, back on Schedule, change A1 to =DATE(2026,11,1), select B2:AF25 and press Delete. Delete clears what was typed and leaves the dropdown, the colours and the weekend shading where they were. If you froze the names as values last month, clear A2:A25 and put the FILTER back in A2 before you start typing, so the list is this month's.

The live month keeps the name Schedule because every formula on the other guides points at Schedule by name. Old months get dated names and nothing points at them, which is what you want from a record. The order is the point. Change A1 first and forget the copy, and October's shifts sit under November's dates with nothing to warn you.

November 2026 starts on a Sunday, so the week of Monday 26 October has six days on October's tab and one on November's. That is the week the page on weekly overtime on a monthly rota is about.

Where a month grid stops

Colour shows you what is on the rota. It does not tell you whether the rota works, and three things stay invisible however carefully you format it.

Rest between shifts. An A on Friday 9 October finishes at 23:00 and an M on Saturday 10 October starts at 07:00. That is 8 hours between them, three short if your rule is 11. On the grid it is a blue cell next to a yellow one. Nothing about it looks wrong.

Two shifts that collide. Two codes in one cell look like a busy day. Whether they overlap depends on their times, and nothing on the grid holds one shift's times up against another's.

Long runs. Monday 5 to Thursday 15 October is eleven days without a day off, and on the grid it is eleven ordinary cells in a row. No single cell is wrong. The run is.

Formulas get part of the way on all three, and the seven mistakes page takes each as far as it goes: a helper row for runs, mirror grids for rest, and a count of the cells holding a double, which finds the doubles without checking whether they collide. Runs and rest do work. The price is four sheets of scaffolding for the rest check alone, a rest check that still compares a day only with the day before it, and a run count that cannot see last month. If the team is small and the rota stays simple, reading the three off the grid by eye once a week is an honest answer too, as long as it really happens every week.

What the add-on does with the same problem

If you would rather not build the grid and keep its rules in order, 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. It does not fill the rota for you: the grid it draws is empty, and the codes are still yours to type. Its dropdown works like Show a warning rather than Reject the input, so a typo still goes in, and what catches it is the Unknown shift code check, one of seven it runs across the whole month:

• Unknown shift code anything not on your Shifts sheet
• Not on Staff sheet a name in the grid with no row on the staff list
• Overlapping shifts two codes in one cell whose hours run into each other
• Double booked a shift starting before the previous one has finished
• Not enough rest under 11 hours by default, measured from the real finishing time
• Over weekly hours over 45 by default, or that person's own limit from the Staff sheet
• Too many days in a row more than 6 by default

Each of those limits is a number you can change on the Settings sheet. 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. The free version also totals hours per person, with overtime hours worked out week by week, and makes a timesheet for one person at a time. There is no weekly view and no clocking in or out: the timesheet comes from the planned grid. 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.

The free version does not expire (labour cost stops after 30 days and the all-at-once timesheet after the first), and there is no email or card to give us: you only need your Google account to make the copy. 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. What you get for the money is a licence code that switches those on in the spreadsheet you already have; it is not a subscription.

Make your own copy, free

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.