Care Home Mandatory Training Tracker: Renewal Dates and AI Overdue Alerts

Track care home mandatory training in one spreadsheet with a row per person and course, a completion date, a renewal interval taken from your own policy, and a formula that works out the due date and status. Each week, send only course names and counts to an AI tool for an overdue summary, and have the manager sign it off.

The tracker is deliberately plain. Missed renewals tend to be a visibility problem, not a software one: they happen when dates live in three places and nobody looks at them until an inspection or a staffing gap. A sheet that calculates status itself, and a short weekly message that arrives whether or not anyone remembers, fixes that. AI's job is the message, not the record.

Follow me on Instagram@sagnikteaches

Layout: a settings tab, a staff tab and a records list

Start with three tabs; a Gaps tab and a Weekly check tab come later. Settings holds the course list and renewal intervals. Staff holds one row per person. Records holds one row per completed course per person. Keeping completion records as a list, rather than a wide grid with a column per course, is what makes the formulas and the weekly summary reliable.

Connect on LinkedInSagnik Bhattacharya

Settings tab

The intervals shown as "your policy" are intentionally blank. Intervals differ by provider, commissioner and role, and some courses have a refresher cycle shorter than the formal certificate. Take them from your training policy, your training provider's certificates and any guidance you follow. Check them with your registered manager before the sheet goes live, and record in the Settings tab who approved them and when.

Subscribe on YouTube@sagnikteaches

Staff tab

Columns: staff code (for example S014), full name, role, start date, line manager, active (yes/no). The staff code is the bridge that lets you send anonymous data to a tool later. Use the code as the key in every other tab; the name appears only here.

Records tab

Columns, in this order so the formulas below line up: staff code (A), course (B), completed on (C), renewal interval looked up from Settings (D), due date (E), status (F), latest flag (G), active (H), then certificate reference or provider, and notes. Add a new row each time a person completes a course; never overwrite an old date, because the history is evidence.

The formulas, line by line

Assume the Records tab starts at row 2, with the staff code in column A, the course in B and the completed date in C. Column D looks up the interval:

D2: =IFERROR(VLOOKUP(B2, Settings!$A$2:$B$20, 2, FALSE), "check course name")
E2: =IF(C2="", "", EDATE(C2, D2))
F2: =IF(E2="", "No date", IF(E2 < TODAY(), "Overdue", IF(E2 <= TODAY()+30, "Due in 30 days", "In date")))

EDATE adds a number of months to a date, so a course completed on 14 March with a 12-month interval comes back as 14 March the following year. Excel returns that result as a date serial number, so format column E as a date or you will see a five-digit number instead. TODAY() returns the current date whenever the sheet recalculates, which includes opening it while calculation is set to automatic, so the status does not go stale. VLOOKUP, IFERROR, EDATE, TODAY and COUNTIFS all exist in both Excel and Google Sheets. If you use another spreadsheet, check the equivalent.

One wrinkle: staff will have several rows per course over the years, and only the most recent counts. Add a "latest?" column that marks the newest row per person and course, and filter on it:

G2: =IF(C2=MAXIFS(C:C, A:A, A2, B:B, B2), "Latest", "Superseded")

Now create a view or pivot with "Latest" rows only. MAXIFS is available in Google Sheets and in Excel 2019 or later, including Microsoft 365. On an older copy of Excel, sort the Records tab by staff code, course and date, and use a simple comparison with the row below.

Column H pulls the active flag from the Staff tab, so leavers drop out of every count without deleting their history. With staff code, full name, role, start date, line manager and active in columns A to F of the Staff tab, the formula is:

H2: =IFERROR(VLOOKUP(A2, Staff!$A:$F, 6, FALSE), "check code")

Also catch the missing ones

A formula on completed records will not tell you that a new starter has never done fire safety, because there is no row to calculate. Add a "Gaps" tab that lists every staff-and-course combination that applies, and a count of "Latest" records for each. Where the count is zero, the status is "Not done". A simple approach is a grid of staff codes down the side and course names across the top, with this formula in each cell:

=IF(COUNTIFS(Records!$A:$A, $A2, Records!$B:$B, B$1, Records!$G:$G, "Latest")=0, "Not done", "ok")

Use conditional formatting to colour "Not done" red. This is the tab most likely to reveal something your current system hides.

Worked example: a 30-person home on a Monday

Illustrative figures. A home has 30 active staff and six mandatory courses. Not every course applies to every role: moving and handling covers 28 care and domestic staff, food hygiene 15 kitchen and named care staff, and first aid 10 named staff, so there are 143 requirements rather than 180. On Monday morning the Latest view and the Gaps tab together show 114 in date, 17 due in the next 30 days, 8 overdue and 4 not done. By course, the 8 overdue are: moving and handling 4, first aid 2, food hygiene 2. The 4 not done are all new starters in safeguarding.

That is a useful picture, but note what the manager actually needs: a short, ranked list of things to do this week. Something like: book a moving and handling session for 4 overdue and the 6 due soon (one session of 10 may be cheaper than two); remind two first-aiders; chase the new starters' safeguarding; check that the two food hygiene staff still work in the kitchen. A good weekly summary says exactly that, and nothing else.

The weekly overdue summary: what goes to the AI and what does not

Staff names, dates of birth and employment details are personal data. The safest design is that the AI never sees names. You have two routes.

Route 1: anonymous counts to a chat tool. A small summary table in the sheet counts statuses by course. The manager copies that table, with no names, into the prompt below.

Route 2: an assistant built into your approved tools. If your provider has approved an assistant inside your spreadsheet or office suite under its business terms, it may read the Latest view directly. Ask your data-protection lead before you try it, and check where the data is processed and whether it is used to train models. The guide to stopping AI tools training on business data explains what to look for.

The summary table the sheet produces

Build it with COUNTIFS over the Latest rows of active staff: for example, =COUNTIFS(Records!$B:$B, A2, Records!$F:$F, "Overdue", Records!$G:$G, "Latest", Records!$H:$H, "yes"). The "Not done" column cannot come from Records, because a missing course has no row there; count it from that course's column in the Gaps grid instead, for example =COUNTIF(Gaps!B:B, "Not done") for the course in column B. Add up the table before you send it: here 114 + 17 + 8 + 4 = 143, the number of requirements, so nothing has fallen through.

The prompt

You are helping a care home manager plan the week's training. Below is an anonymous summary of training status by course (counts of staff, no names). Write a short weekly note for the manager:
1. A one-line headline with total overdue, due in 30 days, and not done.
2. A ranked list of actions, most urgent first, naming each course and how many staff it affects.
3. Where one booking could cover several people, say so.
4. A final line: "Check before acting: [anything that looks odd, such as a course with zero in date]".
Do not invent course names, numbers or dates. If a number looks wrong, say so rather than correcting it. Do not suggest consequences for individuals.

Summary:
[paste table]

A sample, illustrative result: "Headline: 8 overdue, 17 due within 30 days, 4 not done. 1. Safeguarding: 4 not done, the most urgent item. 2. Moving and handling: 4 overdue and 6 due soon; one practical session could cover 10. 3. First aid: 2 overdue, and only 7 in date, so check you still have enough qualified cover on every shift. 4. Food hygiene: 2 overdue, 4 due soon. Check before acting: first aid has only 10 people listed in total, which is lower than other courses." The manager will recognise that "10 listed" is by design, since first aid applies only to named staff, and ignore it. That is the checking step working as intended.

Putting the names back

The manager then opens the Latest view, filters on "Overdue" or "Not done" and reads the names from the sheet. That is the only step that needs names, and it never leaves your tools. Send reminders from your own email, with the person's line manager copied in.

Getting the summary to arrive without anyone remembering

Choose the lightest route that works for your team. The simplest is a calendar reminder every Monday at 08:30 to refresh the sheet, copy the summary table and run the prompt, taking about ten minutes. A step up is a scheduled email that sends the summary table in the body, produced by a spreadsheet script or an automation tool; the tutorial on weekly business summary emails shows the pattern. Only automate once the manual version has run for four weeks and the sheet is trusted.

Whatever you choose, the message should go to the manager and the deputy, not to a shared inbox. A summary that nobody owns is the same as no summary.

Manager sign-off: the line that makes it a record

Add a "Weekly check" tab with a row per week: date, summary pasted in, actions agreed, who signed it off, and a free-text note. A sign-off is a name and date typed by the manager after reading the real list in the Latest view. It shows that someone looked at the real data, not just the AI's note. Add a monthly line for the registered manager to review trends: for example, whether overdue items are falling and whether the same course keeps slipping.

This tab is also what you show in an audit. The monthly audit checklist already includes a training review; the weekly sign-off log means that item is mostly done before the month starts. If you run a mock inspection, the mock inspection tutorial uses exactly this kind of evidence.

Three staff situations the sheet has to handle

A new starter in their first fortnight. Induction is the period where gaps matter most, and the tracker should show it. Add an "induction due by" column on the Staff tab, calculated as start date plus the number of days in your induction policy, and a status that reads "Induction in progress" until that date. Say a new carer starts on the first of the month, has fire safety on day one, moving and handling on day three, and safeguarding scheduled for day nine. On day ten the Gaps grid should show nothing red; if safeguarding is still "Not done" on day 15, the weekly summary should put it at the top. Build the due-by rule from your own policy, not from a figure in a tutorial.

Bank, agency and visiting staff. Staff who are not on your payroll still need evidence that their training is current. Add a "type" column (employed, bank, agency, visiting professional) and an "evidence seen" column recording that you have viewed their certificate or the agency's confirmation and when. The renewal formulas work the same way. The difference is that the date you hold is the date on the evidence, so note where it came from. Do not store copies of certificates in the sheet itself; link to where your own system keeps them.

Role changes. A kitchen assistant who moves to care duties acquires new requirements overnight. Put the role in the Staff tab and the "applies to" column in Settings, then let the Gaps grid read both. A simple approach is a column per course in the Gaps grid that reads "n/a" when the course does not apply to the role. When a person changes role, updating one cell in the Staff tab should light up the new gaps. Test it with a dummy person before relying on it.

A reminder message, drafted from the summary

The weekly summary tells the manager what to do. Individual reminders are a separate job, and the same tool can draft them without seeing a name. Ask it for a template with a placeholder, then merge in names inside your email system.

Write a short, friendly reminder email from a care home manager to a staff member whose [COURSE] training is [overdue / due on DATE]. Keep it under 90 words, say how to book (use the placeholder [HOW TO BOOK]), and ask them to reply once booked. Do not mention consequences or other staff. Use [FIRST NAME] and [MANAGER NAME] as placeholders.

A sample, illustrative result: "Hi [FIRST NAME], a quick reminder that your [COURSE] refresher is due on [DATE]. You can book a place by [HOW TO BOOK], and the sessions this month are on the rota board. Could you reply once you have booked so I can tick it off? Thanks, [MANAGER NAME]". The manager changes "tick it off" if it sounds like nagging, and keeps the version that sounds like themselves. Overdue reminders need a different tone from "due soon" ones, so keep two templates and use the right one.

Recording why something is overdue

An overdue flag is a fact, not a verdict. Add a "reason" drop-down on the Records tab for overdue or not-done items: course booked, on leave, awaiting provider, no place available, not yet chased, other. At the weekly check, "not yet chased" should be a reason that disappears quickly. "No place available" tells you to talk to your provider about more sessions, and a count of it each month is a business case. Say that over eight weeks the "awaiting provider" reason accounts for six of your eleven overdue items: that is a supplier problem, and no reminder email will fix it.

Include the reason column in the anonymous summary as a second small table, so the AI can suggest actions that match the cause, for example "two overdue because no place was available: ask the provider for extra dates". Keep it to counts again.

A quarterly look at the whole picture

Once a quarter, the registered manager should step back from the weekly list. Three questions are enough. How many requirements were in date on the first Monday of each month, as a percentage? Which course is overdue most often? How long, on average, between a course becoming overdue and being completed? All three can be calculated from the Records tab, and the AI can draft the commentary from the three numbers if you give it only the figures. Illustrative, counting "due in 30 days" as still in date: 91%, 93%, 96%; most often overdue is moving and handling; average time to clear an overdue item is 19 days. That tells a manager that the trend is good and that the practical course needs more dates, and it fits on half a page for a provider meeting.

Five ways this tracker breaks

Course names drift. "Fire Safety", "fire safety" and "Fire awareness" are three courses to a formula. Use a drop-down list fed from the Settings tab for the course column.

Dates typed as text. If EDATE returns an error, the date is probably text. Format the column as a date and re-enter one value to test. Where records are imported from a training provider, check the format on the first import.

Leavers and long-term absence. Mark staff inactive rather than deleting them, and filter the summary to active staff. For long-term absence, decide in policy how the clock is treated and record the decision in a note.

The sheet is edited by too many people. One owner, one deputy, edit access for no one else. Share read-only views with team leaders. Keep a copy of the sheet at month end as a dated record.

The AI note is trusted over the data. If the summary says 8 overdue and the Latest view says 9, the Latest view wins. Add a check line: the sum of the table counts must equal the number of requirements. Mismatches usually reveal a typo in a status formula.

Setting it up in an afternoon

An hour for the Settings and Staff tabs, an hour for the Records tab and formulas with ten test rows, an hour to import the existing training matrix and fix date formats, and an hour for the summary table, the Gaps grid, and the first prompt run. Import from your current matrix in batches, and compare totals against the old record. If the new sheet says a certificate is overdue that the old one says is fine, check the certificate itself before changing anything. The data in front of the manager is only as good as the dates typed in.

For the wider picture of how care homes introduce AI safely, the tutorial on AI literacy requirements for staff covers what staff should know about the tools they touch, and my 1:1 consultation can help map a tracker like this around your own course list.

Training tracker questions from care managers

How often does each course need renewing?

That depends on your own policy, your provider's training standards and any guidance from your regulator or commissioners. Put the interval in a settings table in the spreadsheet, so the formulas read it from one place and you can change it without editing every row.

Should staff names go into an AI tool?

Not unless your provider has approved that tool for staff data. A safer design keeps names in your spreadsheet and sends the AI only counts and course names, or a staff code. The manager joins names back using the sheet itself.

Is a spreadsheet good enough, or do we need training software?

A spreadsheet is enough for a home with a few dozen staff if one person owns it and checks it weekly. Dedicated software earns its cost when you want learner reminders, certificates stored with the record and reporting across several sites.

What should the weekly overdue list include?

Overdue items first, then those due in the next 30 days, grouped by course, with the number of staff affected and a suggested action. Names stay in the sheet view the manager uses. The list is a prompt for action, and the manager decides who is booked.

Further reads

• How Much Time and Money Does AI Staff Training Take? — How much staff time AI-related training itself takes.
• AI Tools for Facility Management in a Care Home: Maintenance Logs — Another dated-checks log: maintenance and safety tests.
• Care Home Medication Audit: A Monthly Template and AI Action Log — A monthly audit that also ends in a signed action log.
• Care Home Daily Notes: Better Wording With AI, Checked by Staff — Another record where a manager samples and signs off.
• How to Get Your Staff to Actually Use AI Tools — How to bring a team along with a new routine.
• AI Tool Approval Process: How Staff Request a New AI Tool — How a tool gets approved before it touches staff data.
• Domiciliary Care Software UK: What the AI Features Actually Do — Birdie, CareLineLive and Nourish compared on the AI each really has, what it costs per hour or per carer, and the inspection evidence it creates.
• AI Policy for Aged Care: What to Cover and What Staff Must Not Paste — A checklist for an Australian aged care provider's AI policy: approved tools, the never-paste list, privacy impact assessment, consent and an incident log.
• AI for Dementia Care Providers: Life-Story Profiles and Activity Plans — A provider-side workflow for dementia care: life-story profiles, activity plans and family updates drafted by AI, then consented, checked and filed by staff.
• Care Home Policies and Procedures UK: Keeping Them Current With AI — Use AI to compare each care home policy with current standards, keep tracked changes and a review log, and get staff sign-off before anything is published.
• Care Home Residents' Meeting: Agenda, Minutes and You-Said-We-Did With AI — Plan the agenda, write accessible minutes and keep a you-said-we-did board current, with consent for recording and residents' names handled carefully.
• Care Home Rota Template: Cover, Skill Mix and Gap Checks Using AI — A rota sheet with cover rules and a skill-mix check, plus AI prompts that find gaps and draft messages. The manager decides; nothing changes automatically.
• Care Inspectorate Self-Evaluation: Draft the Evidence Summary With AI — A Scotland-specific worked self-evaluation: quality framework headings, an evidence summary drafted with AI prompts, and a provider who owns every judgement.
• 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.
• 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 pages for EDATE (returns a date serial number), TODAY and MAXIFS (Excel 2019 and later, Microsoft 365), and the Google Sheets function list (EDATE, MAXIFS, COUNTIFS, VLOOKUP, IFERROR, TODAY), all checked 4 November 2026. Renewal intervals are deliberately left to the reader's own policy.