Every Cell Was Green. Seven Were Wrong.

The training matrix was green.

It belonged to a domiciliary care agency in Leeds with 64 carers, and it had been built with real care by Joanne, the registered manager. Sixty-four rows, fourteen required courses, a date in every cell, and conditional formatting that turned a cell green the moment a date was typed into it. It was the sort of spreadsheet people show each other.

The inspector asked to see medication competency evidence for the carers on that week's rota. The agency's own policy, in line with the NICE guidance CQC points providers to, required that competency to be reassessed every year. For seven of the carers on the rota, the most recent assessment was between 13 and 19 months old.

All seven cells were green. They had been green since the day each assessment was typed in, and nothing about the passage of a year had any effect on them, because nothing in the cell was ever compared with anything.

The Safe key question was rated Requires improvement. The local authority paused new referrals until its own monitoring visit was satisfied.

(The agency and Joanne are a composite, drawn from the patterns we see in care providers of this size. The regulator, the ratings and the guidance are real.)

A cell containing a date answers one question: did this happen, and when. That is a perfectly good question. Anybody checking compliance asks a different one, which is whether the thing is still valid today.

The two questions have the same answer on the day the date is typed, and drift apart by one day every day after that. The spreadsheet is never wrong about what it stores. It simply stores an event, and compliance is a state, and the gap between those two widens quietly until somebody from outside the building asks about the state.

The status is typed once and never recalculated. In a surprising number of matrices the green comes from somebody typing Yes, Done or a date, and formatting reacting to the fact that the cell has something in it. Nothing ever compares it with today's date. The matrix is a record of who was compliant on the day each cell was last edited, presented as a record of who is compliant now.

The certificate often never says when it expires. Even a careful matrix with a proper expiry formula fails here. A great many certificates carry only a completion date. The validity period lives in a regulation, a training provider's policy, or the business's own rules, so when somebody logs the certificate there is nothing to type in the expiry column. In the construction firm we mapped this week, 340 of 1,900 certificate records had a blank expiry. A careful formula is written to ignore empty cells, because otherwise every unfinished row turns red. So a blank expiry and a valid one end up looking identical: both show nothing, and nothing has no colour.

A missing requirement creates no row. The matrix lists the training people have. When a carer starts taking on medication calls, or a labourer moves onto a scissor lift, the requirement arrives with the task. If nobody adds a column or a row, there is no empty cell to turn red. The absence of a qualification is invisible in a system that only records the qualifications that exist, which is a sentence that sounds obvious and turns out to describe most compliance files in small businesses.

Most agencies, practices and contractors do check their matrix. Somebody reviews it monthly, or before an inspection, or when a client asks.

The review consists of looking at the matrix. The matrix is green. The quality control process for the compliance record is, in other words, the compliance record, and it passes itself every time. The first independent check most businesses ever receive is the inspection, which is a fairly expensive place to discover that the formula was comparing nothing with today.

There is a fuller version of this argument in what process bottlenecks actually cost: the loss sat in plain view the whole time, with nowhere in the building that would add it up.

The calculation itself has never been hard. Completion date plus validity period, compared with today, is one line in any spreadsheet. What made it impractical was everything around that line. Somebody had to read each certificate, work out what kind it was, find the rule for how long it lasted, type both, and keep doing it as hundreds of new photos arrived by email and text.

Reading a phone photo of a certificate and saying what kind it is, who it belongs to and what date is printed on it is now a job a vision model does reliably, with a confidence score a person can check. The model reads. A rules table, written by people who know the rules, sets the validity. A nightly calculation does the arithmetic. The Phoenix contractor in this week's Blueprint runs the whole thing for about $210 a month, and its spreadsheet had previously been finding 7 expired certificates out of 61.

The rules table is the work. Somebody has to write down, for every certificate the business relies on, how long it lasts and whose rule sets that: the regulation, the training provider, the client contract, or the business's own policy. Where those disagree, the strictest one wins, and somebody has to know that they disagree.

In most businesses that knowledge exists, spread across the registered manager, two senior staff and a folder of client contracts. It is the same problem as rules nobody wrote down, in a different column. A small business can do it in a week. It is also the reason a cheap compliance tool bought off the shelf usually disappoints: it arrives with a rules table written for somebody else's industry.

Open your training or certification matrix and pick one course that you know must be renewed.

Add one column beside it. In Excel or Google Sheets, for an annual check where the completion date sits in column C, that is =IF(C2="","MISSING",IF(EDATE(C2,12)<TODAY(),"EXPIRED","")). It adds twelve months to the completion date, compares the result with today, and refuses to treat an empty cell as fine. Count how many green cells now sit next to the word EXPIRED.

Then count the MISSING rows belonging to people whose job needs that course. Those are the ones the inspector will find before you do.

The matrix was accurate on every day something was typed into it. The inspector came on a different day.

Our position, which we are prepared to defend: a training matrix that is entirely green is less trustworthy than one with three red cells, because the red ones prove somebody has looked. What is the longest you have known a lapsed certificate sit on a matrix still showing as current? Months, please, rather than the story, although we will happily take the story as well. Leave it in the comments below or reply to Thursday's newsletter. We will publish the range next week, anonymised unless you tell us otherwise.

If your quality or health and safety lead keeps the matrix, forward this to them. Ideally before an inspector does.

The AdAI newsletter maps one of these bottlenecks end to end every week, free to read. Subscribe here.