2026-10-20・Automation / Small business / Excel

Build a weekly staff shift schedule in Google Sheets (hours per person, gaps highlighted)

If you have a small team, the weekly rota often lives on a whiteboard, a paper sheet on the wall, or a group chat. Then someone swaps a shift, someone else misses the message, and Saturday morning is short of people.

A shared Google Sheet fixes most of this. Everyone sees the same version, hours add up by themselves, and gaps turn red before they become a problem. This guide uses Google Sheets, but the formulas also work in Excel. It takes about 20 minutes to set up, and then you copy it each week.

This guide only covers the mechanics of a rota. Rules on hours, breaks and rest time depend on your country and your contracts, so check those separately.

What you will build

• A Codes tab that defines your shifts once (for example Early, Late, Off).
• A weekly Rota grid: one row per person, one column per day.
• Total hours per person, worked out by formula.
• A coverage row for each shift that turns red when you are short and amber when you have more people than needed.
• A view-only link for your team.

Step 1. Define your shifts on a Codes tab

Create a tab called Codes with these headers in row 1:

Add one row per shift type, for example:

• E, 07:00, 15:00, 30
• L, 14:00, 22:00, 30
• OFF, leave the times blank

Format columns B and C as times. In Google Sheets, use Format > Number > Time. In Excel, use the number format box on the Home tab.

In E2, paste this formula and copy it down:

=MOD(C2-B2,1)*24-D2/60

Times in a spreadsheet are fractions of a day, so multiplying by 24 turns them into hours. MOD(..., 1) handles shifts that cross midnight, such as 22:00 to 06:00. The break is then taken off. For OFF, the result is 0.

If the Hours column shows a time instead of a number, change its format to a plain number.

Step 2. Build the weekly grid

Create a tab called Rota. Set it up like this:

• A1: Week starting. B1: the Monday date, for example 2026-10-26.
• B2: =$B$1, C2: =B2+1, and copy C2 across to H2. These are the seven dates.
• B3: =TEXT(B2,"ddd"), copied across to H3. This shows Mon, Tue and so on.
• A3: Staff, I3: Hours.
• A4 to A11: one staff name per row.

Each cell from B4 to H11 will hold a shift code for that person on that day.

Add a dropdown so only valid codes can be entered:

1. Select B4:H11.
2. Open Data > Data validation (in Excel, Data > Data Validation).
3. In Google Sheets, choose a dropdown from a range and enter Codes!$A$2:$A$10. In Excel, choose List and enter =Codes!$A$2:$A$10 as the source.

Now each person's week is just a row of codes, like E, E, OFF, L, L, OFF, OFF.

Step 3. Add up hours per person

In I4, paste:

=SUMPRODUCT(COUNTIF(B4:H4,Codes!$A$2:$A$10),Codes!$E$2:$E$10)

How it works: COUNTIF counts how many times each code appears in this person's row. SUMPRODUCT multiplies each count by that shift's hours and adds them up. Two Earlies and two Lates at 7.5 hours each gives 30.

Copy it down to I11. When you change a code in the grid, the hours update straight away.

For a team total, put =SUM(I4:I11) in I12.

Step 4. Check coverage for each shift

Below the grid, add a small coverage block:

• A13: Early on shift. A14: Late on shift.
• B13: =COUNTIF(B$4:B$11,"E"), copied across to H13.
• B14: =COUNTIF(B$4:B$11,"L"), copied across to H14.
• I13: how many people you need on an Early shift, for example 2. I14: the same for Late.

Now you can read across and see how many people are on each shift each day, next to how many you need.

Step 5. Highlight gaps and overstaffing

Google Sheets:

1. Select B13:H14.
2. Open Format > Conditional formatting.
3. Under "Format cells if", choose Custom formula is and paste:

=B13<$I13

4. Pick a red fill and click Done.
5. Add another rule on the same range with this formula and an amber fill:

=B13>$I13

Excel: select B13:H14, choose Home > Conditional Formatting > New Rule, then Use a formula to determine which cells to format, and add the same two rules.

Red means a shift is short. Amber means more people than planned, which might be fine for a busy day, or might be a mistake worth checking.

The $I13 keeps each rule pointing at the "needed" number in column I, on the same row it is checking.

You can also colour the grid itself so the week is easy to read. Select B4:H11 and add rules like =B4="OFF" in grey and =B4="L" in light blue.

Step 6. Share it view-only with your team

You want staff to see the rota, but only you (or a manager) should change it.

1. Click Share in the top-right corner of the sheet.
2. Add your team's email addresses.
3. Set their role to Viewer and send.

Viewers can open the sheet on their phone in the Google Sheets app or a browser, and they always see the latest version. They cannot edit it.

If a supervisor needs to edit the grid but not the formulas, give them Editor access and protect the formula areas. Select the Codes tab or the hours and coverage cells, then use Data > Protect sheets and ranges to limit who can edit them.

Using it each week

• Make a new week: right-click the Rota tab and choose Duplicate. Rename the copy, for example "Week 2026-11-02", and change the date in B1. All the dates and days update.
• Fill in codes: start from last week's pattern and adjust for holidays and requests.
• Check the coverage rows: fix anything red before you share.
• Send a short message: "Next week's rota is up." Everyone opens the same link.

Keeping old weeks as separate tabs also gives you a simple record of who worked when.

What else could you automate?

A rota is one of many weekly admin jobs. Our free Small Business Automation Check asks about your country, industry and daily tasks, and shows which jobs are easiest to automate and where to start.