Tracking licence expiry without a spreadsheet that nobody opens

A licence tracker is one of the first things any security contractor builds. It is also one of the first things that quietly stops being accurate, because a spreadsheet only tells the truth on the day someone updates it.

The three failure modes

Trackers do not fail dramatically. They drift, in three fairly predictable ways.

• –Staleness — an officer renews, the certificate goes in an inbox, and the sheet still shows the old date
• –Distance — the sheet lives with the compliance lead, and the person building the roster never sees it
• –Silence — the sheet has no way to raise its hand, so an approaching date only surfaces if someone goes looking

Of the three, silence causes the most damage. A tracker that is perfectly accurate and never consulted prevents nothing.

What good looks like

The useful test is not "do we have a record?" but "would this have stopped an unlicensed officer being rostered last Tuesday?" That reframes the problem away from record-keeping and towards the decision point.

• –The date is attached to the officer, so it travels with them between sites and contracts
• –The system raises the alert rather than waiting to be asked
• –The warning arrives with enough runway for a renewal to actually complete
• –Whoever builds the roster can see licence status without opening anything else

Evidence, not just alerts

Alerts prevent the incident. Evidence is what you produce afterwards. Being able to show when a licence was verified, by whom, and what it said at the time, is a different capability from knowing today's status — and it is the one that matters in an assessment.

Related: what actually happens when an SIA licence expires mid-contract.

Why the spreadsheet fails specifically here

Licence tracking has a property that makes spreadsheets unusually weak: the consequence of missing a date is immediate and absolute. A missed maintenance task is a problem to catch up on; an officer on shift without a valid licence is a situation that has to end now. A tracking method that depends on somebody opening a file is not well matched to a risk with no tolerance in it.

The second issue is that the population changes constantly. Officers join, leave, move between contracts and work for more than one employer, and each movement is a chance for a row to be added without a date, or a leaver to be left in place inflating the compliant count.

What a tracked date actually needs around it

A date on its own is not tracking. What makes it useful is a named owner, notification far enough ahead to act on, a record of whether renewal has actually been started, and escalation if the date approaches with nothing happening. Most spreadsheets hold the first element and none of the rest, which is why they appear to work right up to the point they do not.

Common mistakes

• –Holding the date with no notification attached to it
• –No record of whether a renewal has actually been applied for
• –Leavers left on the list, inflating the compliant figure
• –Officers added without their expiry date being captured
• –One person maintaining it, with no cover when they are away
• –No escalation when a date approaches and nothing has happened

What separates tracking from recording

Recording holds the date. Tracking does something with it — surfaces it ahead of time to a named person, records whether action has been taken, escalates when the date approaches with nothing happening, and prevents the officer being rostered once it passes.

Most organisations that believe they are tracking licences are recording them. The distinction is invisible until the day a date passes unnoticed, at which point it is the only thing that mattered.

One practical test: check it at the moment of rostering

Watch somebody build next week's rota and note whether licence status is checked at the point an officer is assigned, or at some other time by somebody else.

A record that is accurate when opened is not the same as a check that happens when the decision is made. Most contractors have the first and believe they have the second, and the gap is invisible until an officer is rostered onto a shift that starts after their licence ends.

• –Put the check at the point of assignment, not in a separate routine
• –Warn when a licence expires part-way through a rostered shift
• –Hold a notification, not just a date
• –Remove leavers promptly so the compliant count is real
• –Keep an audit trail of what changed and when