Build the rota as a grid of shifts against staff, add a rules sheet that states minimum cover and the skills each shift needs, then have AI read the filled rota and list every gap, such as no medication-trained senior on a late. The manager decides what to change, and nothing alters a shift automatically.
The reason to separate the rules from the people is that a rota can look full and still be unsafe. Eight names on a Saturday early mean nothing if none of them is signed off to give medication or if two are on a first shift after a night. A short, written set of rules is what lets either a formula or an AI tool say something useful.
The rota sheet: three tabs and what goes on each
A plain spreadsheet is enough, in Excel or Google Sheets. Keep it to three tabs, because more tabs invite drift between versions.
| Tab | Holds | Who edits it |
|---|---|---|
| Staff | Staff code, role, contracted hours, competencies (medication, first aid, moving and handling, nurse), working restrictions | Deputy manager, when something changes |
| Rota | One row per staff code, one column per day, a shift code in each cell (E, L, N, LD for long day, OFF, AL, SICK) | Manager or deputy only |
| Rules | Minimum cover per shift, required skills per shift, maximum consecutive shifts, minimum rest between shifts | Registered manager, after the dependency review |
Use short staff codes (S01, C07) on the sheet that leaves your system, and keep the key that links codes to people in a separate, restricted place. This protects staff and costs you nothing, since the checks do not need names.
Write the minimum cover rules before anything else
Rules come from your own assessment of residents' needs, your registration and your regulator's current guidance, not from a template and not from an AI tool. Take them from your dependency review, write them as numbers and keep them short. Here is a filled-in example for a made-up 32-bed residential home, labelled as illustration only.
| Shift | Hours | Minimum carers | Minimum seniors | Must include |
|---|---|---|---|---|
| Early (E) | 07:00 to 15:00 | 5 | 1 | One medication-signed senior; one first aider |
| Late (L) | 14:00 to 22:00 | 4 | 1 | One medication-signed senior; one first aider |
| Night (N) | 21:30 to 07:30 | 3 | 1 | One medication-signed senior; two awake staff at all times |
Add three limits that managers often forget to write down: no more than five shifts in a row, at least 11 hours between the end of one shift and the start of the next, and no staff member scheduled on a role above their competency. The 11-hour figure is a common working-time rest period but your contracts and the current rules decide; check them with your HR adviser and enter whatever applies. The tutorial on rules to enter so a rota stays legal goes through the ones to confirm.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowFour weeks of cover for one 32-bed home, with the sums
Illustrative figures. The home has 6 seniors, 18 carers and 2 night leads. The rules above ask for 5 carers and 1 senior on each early, 4 carers and 1 senior on each late, and 3 carers and 1 senior on each night. That is 12 carers and 3 seniors a day, or 84 carer shifts and 21 senior shifts a week, 105 in all before leave and sickness.
The manager fills in the four-week grid and finds the first problem without AI: 21 senior shifts across only 6 seniors is 3.5 each, but two seniors work part time and cannot cover more than 3 shifts. That is a sum, and a formula catches it. The harder problems are combinations, like week 3, where one senior is on annual leave, another is sick, and the medication-signed one is the only person on the late on both Saturday and Sunday. A formula can count it. An AI tool reads the whole grid and writes it as a message the manager can act on.
Counting cover with formulas first
Use formulas for anything that is a plain count, because they are exact and free. Below is the shape of the check for early shifts on one day, with column B holding role and column D holding that day's shift codes. Adjust the ranges to your sheet.
Carers on Early: =COUNTIFS(Rota!D$2:D$40,"E",Staff!$B$2:$B$40,"Carer")
Seniors on Early: =COUNTIFS(Rota!D$2:D$40,"E",Staff!$B$2:$B$40,"Senior")
Med-signed on E: =COUNTIFS(Rota!D$2:D$40,"E",Staff!$E$2:$E$40,"Y")
Gap flag: =IF(OR(carers<Rules!$B$2, seniors<Rules!$C$2, medsigned<1),"GAP","OK")One trap catches many homes. COUNTIFS pairs its ranges cell by cell, and Microsoft's documentation requires each extra range to have the same number of rows and columns as the first. So row 2 of the Rota tab must hold the same staff code as row 2 of the Staff tab, all the way down. Sort one tab and not the other and every count is quietly wrong. Keep the staff code in column A of both tabs and add a check cell such as =SUMPRODUCT(--(Rota!A2:A40<>Staff!A2:A40)), which counts mismatched rows and should read 0. In the gap flag, the words carers, seniors and medsigned stand for the cells holding those three counts.
Colour the flag cell red when it reads GAP. Do the same for Late and Night. Now the manager sees at a glance which days fail, and the AI is used for the judgement-heavy next step, not for counting. If you prefer to learn spreadsheet checks like these, the older ChatGPT and Excel guide shows how to ask for formulas safely.
The AI skill-mix check, prompt and sample output
Skill mix is a mix of rules that are easy to state and awkward to count: a first aider, a medication-signed senior, someone who can drive the minibus on a Tuesday, a person competent in using the hoist. Paste the Rules tab, the Staff tab with codes only, and the four-week grid. Ask for a report, not a new rota.
You are checking a care home rota for the manager. Do NOT propose
a new rota and do NOT move anyone.
Using only the RULES, STAFF and ROTA below, list every shift that
breaks a rule. For each give:
- date and shift
- which rule fails and by how much
- who is on it (codes only)
- two possible fixes, each naming the person it would affect and the
rule it might break
If a fix would break a rest or consecutive-shift limit, say so.
If you are unsure, say what information is missing. Never invent
a competency that is not in STAFF.
[RULES]
...
[STAFF]
...
[ROTA]
...An illustrative output for week 3: "Sat 14, Late. Fails: no medication-signed senior (rule: at least 1). On shift: S03 (senior, not signed), C07, C11, C12, C14. Fix A: swap S05 (medication-signed, currently Early on Sat) to Late; this puts S05 on Late the day after an Early, which is allowed, but the Early then has only 4 carers (needs 5). Fix B: ask S02 (medication-signed, OFF) to cover; check S02's contracted hours first. Sun 15, Night: 3 carers, meets minimum, but S09 and C03 are both on their sixth consecutive shift (limit 5)."
What you would check: the AI found three real problems, and the fixes are sensible options. What it cannot know is that S02 has a childcare arrangement on Saturdays or that C03 asked for those shifts for the money. The deputy rings people and decides. That is the point of the "manager decides" rule.
Gap messages: drafting the ask, not sending it
Once the manager has chosen a fix, the AI can draft the message to the person asked, a message to the bank or agency list, or a note for the handover board. Keep every message polite, optional and specific, and use "[first name]" until you add the real one. Never let a tool send them.
Draft a short, friendly message from the deputy manager to a
member of staff asking if they could cover a late shift on
Saturday 14, 14:00 to 22:00, as an extra shift. Say it is a request,
they are free to say no, and that I will confirm either way by
5pm tomorrow. No pressure language. Under 60 words.A sample result: "Hi [first name], would you be able to cover the late shift this Saturday (14:00 to 22:00)? It is an extra shift and completely fine to say no. I will confirm either way by 5pm tomorrow. Thanks, [your name]". What you would fix: nothing in tone, but add the pay rate or time off in lieu if your policy states one, and check the person has not worked 11 hours earlier.
Four more cases the template should catch
These are the combinations that go wrong most often. Each can be turned into a line in the Rules tab and tested.
Case one is the double absence. Two seniors off in the same week, one planned leave and one sick call on a Monday. The AI report lists every shift where the remaining seniors fall under the minimum and offers three possible patterns, for instance using the deputy as a working senior on two shifts or asking the unit lead to cover a late. Case two is the new starter. A person in their first fortnight is supernumerary or shadowing, so they should not count towards minimum carers. Add a "counts towards cover: Y/N" column in the Staff tab and the formula ignores them until the date they are signed off.
Case three is the training clash. Mandatory training on a Wednesday takes six carers off the floor for a morning. If the training tracker feeds the rota, the gap shows before the day. The mandatory training tracker gives the dates to paste in. Case four is agency cover: agency staff may not hold your medication sign-off even though they fill a carer slot. Mark them as carers with no competencies, and the check will tell you that a late with three agency carers and one senior is thin on skill even though it meets the headcount.
Fairness, preferences and leave built into the sheet
A rota that is technically full can still be unfair, and unfair rotas lose staff, which is the cause of most later gaps. Add two columns to the Staff tab: weekend shifts worked in the last eight weeks, and any agreed pattern (for example "no nights, childcare" or "prefers lates"). Ask the AI to read the four-week grid and report who is carrying more than their share of weekends and nights, as a list of codes and counts. It is counting, which a formula can do too, but the plain-English summary helps a manager see it at a glance.
Illustrative result: "C04 has worked 7 of the last 8 weekends; C09 has worked 2. S05 has covered 6 nights in four weeks; the average is 3." The manager can rebalance at the next draft, and staff who see that weekends are shared are more willing to take an extra when a gap appears. For leave, keep a simple limit in the Rules tab, such as no more than two carers and one senior on annual leave on the same day, and ask the AI to flag any new leave request that breaks it before you approve it. Say no or offer a different date in your own words; the tool checks the arithmetic only.
A night-shift example: where the headcount hides the risk
Take a Tuesday night in the same home. The rota shows 4 staff against a minimum of 3 plus a senior, so the formula says OK. The AI skill-mix check reads the competencies and reports that the senior, S06, is medication-signed but the other three are a new starter in week two (not counting), an agency carer unfamiliar with the building and a carer who has never done a night alone. On paper there are four people. In practice there are two people who can act independently, one of whom is the senior who must also do the medication round.
That is a judgement for the manager. She might swap the agency carer for an experienced carer from the bank list, or move the new starter to shadow on a day shift and ask a late carer to extend. A formula would not have noticed, which is the value of asking the tool to read competencies and experience. Add an "experience level" column (new, established, lead) to the Staff tab so the check has something to read.
The weekly routine, in 20 minutes
Once the template is built, the weekly job is short. On Thursday the deputy updates leave, sickness and training in the sheet, and the formulas turn red where cover fails. Then, in about ten minutes, she pastes Rules, Staff codes and the next two weeks into the AI tool for the skill-mix report. She reads it against the sheet, makes her decisions, and sends two or three gap messages. On Friday morning she publishes the rota to the team, with the version date at the top, and files the previous week's copy.
Keep a one-line log each week: date, gaps found, gaps fixed, gaps left open and why. After eight weeks you can see whether the same shifts keep failing. If every second Sunday late is short, the problem is the pattern, not the individual week, and the answer might be changing contracts or recruiting for weekends rather than asking people for another favour.
What to do on the morning when the rota fails
A sickness call at 06:00 is where the template earns its place. The deputy updates the rota with SICK, the gap flags turn red, and the AI report for that day takes a minute to produce. It lists the gaps, the options and the consequences. The deputy then works down a fixed order that you write into the Rules tab so it does not depend on who is on call: first, ask staff who are on a day off and have volunteered for extras; second, ask the bank list; third, use agency staff booked through the usual route; fourth, escalate to the registered manager if the minimum cannot be met, because that is a safety decision not a rota one.
Write down the escalation line in numbers: "if early carers fall below 4, call the registered manager before 06:30". A threshold removes the guessing at a stressful moment. The AI does not hold this authority. It can only tell the deputy that the threshold has been crossed.
The neat rota that broke a rest rule
A manager asks an AI tool to "fix the gaps" in a rota and pastes the grid with no rest rule. It moves a carer from a Monday night to a Tuesday early, which closes the gap neatly, and which leaves about 7 hours between shifts. The rota looks fine and the carer comes in exhausted. The tool did what the instruction said, and the instruction left out the limit.
The fix has two parts. Always include the Rules tab in the paste, and keep the "report only" wording so no new rota is produced. Also run a plain formula that flags any gap shorter than 11 hours between shifts, because rest rules are exact and should not depend on an AI reading the grid correctly each time.
Keeping staff data safe while you do this
A rota is staff data, and sometimes it reveals sickness, caring responsibilities or religious observance. Paste codes, not names, and take out free-text notes such as "off for appointment". Keep the tool to business plans where the vendor states content is not used for training, and make sure only the manager and deputy can open it. Do not upload the rota to a tool your provider has not approved. The comparison of AI scheduling and a rota spreadsheet explains when dedicated software is worth paying for instead.
Three rota measures to review every four weeks
Choose three measures and look at them every four weeks. First, the number of shifts that started below minimum cover, ideally zero and recorded with the reason when not. Second, the number of last-minute (under 24 hours) changes. Third, the time your deputy spends building and fixing the rota, which should fall as the checks catch problems early. If a four-week review shows, for example, 5 gaps in the first month and 2 in the second, and the cause of the remaining two was sickness rather than planning, the template is doing its job.
Review the Rules tab at the same cadence, and any time dependency levels change in the home. A rota is only as safe as the numbers it is checked against. If you would like help setting the checks into the sheet you already have, my AI implementation consultation is built for that kind of job, and the domiciliary care rota template tutorial covers the version for staff who travel between visits.
Rota questions from care home managers
Should AI be allowed to change shifts on its own?
No. Let it read the rota and report problems, such as a late shift with no medication-trained senior. The manager or deputy then decides, speaks to the person affected and updates the sheet. Automatic changes bypass consent, rest rules and the judgement a manager brings.
What does skill mix mean on a care home rota?
It is the combination of roles and competencies on each shift, for example seniors, carers, trained nurses, first aiders and people signed off to give medication. A shift can have enough heads and still lack the right skills, which is what a skill-mix check catches.
Is it safe to paste staff names into an AI tool?
Use initials or staff codes in the version you paste, and keep the names-to-codes key in your own system. The AI needs shift patterns and competencies, not identities. Check your provider's data terms before using any tool with staff data.
Can a spreadsheet do this without AI?
Yes. Formulas can count cover and flag shortfalls. AI adds value in reading messy rotas, explaining the gaps in plain English, suggesting options for the manager to consider and drafting the messages, not in the counting itself.
Further reads
- Nursery Staff Rota: Ratios, Breaks and Cover Built With AI — A ratio-driven rota, useful to compare with care cover rules.
- Supported Living Software: Rotas, Support Plans and Where AI Fits — When rota software and support plans need to meet.
- Care Home Software UK: An AI Features Checklist Before You Sign — Questions to ask before buying rota or care planning software.
- AI for Care Homes: A Registered Manager's 90-Day Implementation Plan — Place the rota check within a wider 90-day plan.
- Artificial Intelligence Workforce Automation: Rotas, Timesheets, Leave — Automating timesheets and leave alongside the rota.
- Care Home Activities Calendar: Plan a Month With AI and Resident Input — Turn coded resident interest sheets into a monthly activities calendar with group and one-to-one slots, a printable version and a staff check.
- Care Home Audit Checklist: A Monthly Round Logged and Actioned With AI — A monthly care home audit checklist with scoring, an AI summary built from counts only, and an action log with owners and dates.
- Care Staff Supervision Template: Notes and Action Logs With AI — A care-specific supervision agenda, record format and action log, with AI drafting the actions from your notes while staff data stays inside approved tools.
- Can AI Write a Care Home Fire Risk Assessment? What Stays With You — Where AI helps with a care home fire risk assessment (structure, action log, evacuation notes) and the judgements a qualified assessor must keep.
- How Can AI Help in Care Homes? Ten Back-Office Uses — Ten care home back-office jobs where AI saves real time, each with an example, a check and a clear line on what stays with staff.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: Microsoft Support, COUNTIFS function (range size rule), and official working-time guidance on daily rest (11 hours between working days), both read 4 November 2026. Staffing levels and working-time rules must come from your own dependency assessment, contracts and regulator guidance, read on their current pages.