Why Spreadsheet Rosters Fail at 24/7 Security Coverage

Spreadsheet rosters fail at 24/7 security coverage because a spreadsheet can record a shift assignment but cannot refuse one. Every rule that actually matters in security rostering is a refusal: this officer cannot work this post because their certificate lapsed, cannot work this shift because it leaves them under eight hours rest, cannot work this week because it breaches the hour cap, cannot work six days running at this site because the client contract forbids it. A spreadsheet accepts all of those entries silently. The rules exist only in the head of whoever is building the roster, and they get applied unevenly under time pressure, which is precisely when coverage decisions are being made.

That is the whole argument. Everything below is detail on what the refusals need to be, why they have to fire at the moment of assignment, and what has to happen when a gap opens at 22:00 on a Saturday.

The shape of the problem

Consider a modest agency: twelve contracts, thirty-one posts, of which nine are 24-hour posts covered by two 12-hour shifts. That is 53 shifts a day before any relief, roughly 1,600 shift assignments a month, each of which has to satisfy simultaneously:

• The officer holds the grade the post requires.
• The officer holds every certificate the post requires, currently valid on the shift date.
• The officer’s work pass is valid on the shift date.
• The assignment does not breach maximum consecutive working days.
• It leaves the minimum rest interval since their previous shift.
• It does not push them past weekly or monthly hour caps.
• Their mandatory weekly rest day is preserved.
• Client-specific rules hold, such as a cap on consecutive days at one site.
• The officer is actually available and has not booked leave.

Nine constraints across 1,600 assignments is 14,400 checks a month. Done by hand, they are not done. What happens instead is that experienced schedulers apply the two or three constraints they consider most important, rely on memory for the rest, and absorb the errors as they surface. The system works, in the sense that posts get covered, but the failure mode is invisible until something forces an examination.

Constraints have to be configuration, not code

The temptation when building this is to encode the rules the current contracts require. That produces a scheduler that works until the fourteenth contract arrives with a rule nobody anticipated, such as a requirement that no officer works more than four consecutive night shifts at a data centre, or that a specific site rotates its lobby officer monthly for security reasons.

The rules that need to be configurable rather than fixed include officer availability and preferred sites, maximum travel distance where that matters, maximum weekly and monthly hours, maximum consecutive days, minimum rest between shifts, mandatory rest-day frequency, required grade and required certificate combinations per post (with support for alternatives, since a post may accept either of two qualifying certificates), and client-specific rules attached to the contract rather than hard-coded.

Fairness rules belong in the same layer and are usually forgotten. Caps on how many night shifts or public holiday shifts a single officer takes per month, minimum guaranteed hours for part-time officers where contractually agreed, and rotation policies to avoid concentrating unpopular shifts on whoever is least likely to object. These are not soft concerns. Unevenly distributed night work drives attrition, and attrition is the most expensive problem an agency has.

Generation, then honest gap reporting

A roster engine generates a draft for a period from the contracted posts and shift patterns, respecting every constraint above. The important design decision is what it does when it cannot satisfy them all.

The wrong answer is to produce a complete-looking roster by relaxing constraints silently. The right answer is a draft with explicit gaps, classified by severity, so the scheduler knows exactly what remains unsolved. A roster showing twenty-eight open shifts, six of them critical, is more useful than a roster that appears full but contains four rest-day violations nobody has noticed.

Gap severity is worth defining deliberately. A critical gap is an unfilled shift at a post with contractual minimum coverage starting within 24 hours. A high gap is an unfilled shift within the week. A medium gap is an under-staffed shift where minimum headcount is met but the contracted level is not. Different severities warrant different responses, and lumping them together means the urgent ones get the same attention as the routine ones.

Manual adjustment is where most systems leak

Every roster gets adjusted by hand. That is not a failure of the engine; it reflects that schedulers hold context the system does not. The question is whether manual adjustment is validated.

A drag-and-drop change has to run the same constraint checks as automatic generation, in real time, and either warn or block depending on whether the violated rule is advisory or hard. If manual edits bypass validation, then within a fortnight the standard workflow becomes generate-then-override, and the constraint engine is decoration.

Roster states matter for the same reason. Draft, published, locked. Publishing should enforce a configurable minimum advance notice, commonly seven to fourteen days, with a warning when that is breached rather than a silent acceptance, because late-published rosters are the root cause of a large proportion of no-shows. Version history should record who published, when, and what changed against the previous version, because roster disputes are common and memory is not evidence.

The 22:00 Saturday problem

A guard does not show for a night shift. The post has contractual minimum coverage. The supervisor has perhaps forty minutes.

This is the scenario that determines whether a rostering system is worth its licence fee, and it decomposes into three capabilities.

Detection. The system knows the officer has not clocked in within the grace window and raises it without waiting for someone to notice. Late and absent detection tied to expected shift start is the difference between forty minutes and four minutes of usable response time.

Ranked suggestion. Not a list of everyone, but the officers who are actually eligible, ranked. Eligibility filters on grade, valid certificates, hour caps and rest intervals. Ranking sorts on proximity to site, overtime cost impact, fairness position, and past reliability. A supervisor with a ranked shortlist of four makes a better decision in two minutes than one working from a full contact list.

Broadcast. One action that reaches the eligible pool through a channel they will actually see at 22:00 on a Saturday, which is not email and not a portal notification. In Singapore that is WhatsApp or SMS, with a reply that claims the shift and creates the assignment, subject to the same eligibility checks.

The self-service side reduces how often this happens at all. Officers who can see their own shifts, request swaps, claim open shifts within policy caps and submit leave through an app generate fewer surprises, because most no-shows are foreseeable and go unreported only because reporting them is awkward.

Approval rules that do not become a bottleneck

Self-service creates an approval load. If every swap needs a supervisor decision, the supervisor becomes the constraint and officers stop using the feature.

The workable split is rule-based auto-approval for low-risk changes and mandatory review for the rest. A swap between two officers of the same grade at the same site with no compliance impact and no overtime consequence can auto-approve. A claim that pushes an officer toward an hour cap, or a swap affecting a safety-critical post, routes to a human. The categories need to be configurable, because what counts as safety-critical is a client-specific judgement.

How Moxogo implements it

Moxogo Security implements rostering as a constraint-based engine where availability, grade and certificate requirements, consecutive-day limits, minimum rest intervals, weekly hour caps, rest-day rules and client-specific restrictions are configuration rather than code. Constraints are validated on every path into a shift, including manual drag-and-drop adjustment and guard shift claims, with structured refusal codes so a blocked assignment explains which rule it violated.

Rosters move through draft, published and locked states with configurable minimum advance notice and full version history. Coverage is computed continuously per site, post and contract, gaps are classified by severity, and replacement suggestions are ranked by eligibility and suitability rather than presented as an unfiltered list. Shift opportunities can be broadcast to an eligible pool through WhatsApp, SMS or in-app notification, with keyword replies that confirm or claim subject to the same eligibility checks.

The module’s stated targets are at least 98 percent post coverage on contracted shifts and a halving of manual roster creation time against spreadsheet workflows. Those are design goals the engine is built against rather than published customer averages, and the sensible thing during evaluation is to have them demonstrated on your own post definitions, shift patterns and client rules.

Labour hours flowing out of the roster connect to the Human Resources and Project Management modules, which is what allows verified hours to reach payroll and client invoicing without a re-keying step.

A practical test

Take last month’s roster as actually worked. Count how many assignments breached any of the nine constraints listed at the top of this article. If you cannot count them, that is the answer: the constraints were never enforced, only intended. Most agencies running this exercise for the first time find breaches in the low single-digit percentages, which sounds tolerable until you consider that each one is either a compliance exposure or an officer working unsafely tired.

Frequently Asked Questions

Why can a spreadsheet not handle security rostering? Because every rule that matters is a refusal, and a spreadsheet cannot refuse an entry. Certificate validity, rest intervals, hour caps and client rotation rules all have to prevent an assignment, not merely record one. In a spreadsheet those rules exist only in the scheduler’s memory and get applied unevenly under time pressure.

Should a roster engine produce a full roster or show gaps? Show gaps, classified by severity. A draft with twenty-eight explicit open shifts is more useful than an apparently complete roster that quietly relaxed rest-day rules to fill them, because the second hides exactly the information a scheduler needs.

Do manual roster edits need the same validation as automatic generation? Yes, in real time. If drag-and-drop adjustment bypasses constraint checking, the standard workflow becomes generate-then-override within about two weeks and the constraint engine stops having any effect.

How should a system handle a no-show forty minutes before a shift? Detect it automatically from the missed clock-in rather than waiting for someone to notice, present a ranked shortlist of genuinely eligible replacements filtered on grade, certificates, hour caps and rest intervals, and broadcast the opportunity through a channel officers read at that hour, which in practice means WhatsApp or SMS.

Why do fairness rules belong in the scheduling engine? Because unevenly distributed night and public holiday shifts drive attrition, and attrition is the most expensive recurring problem an agency has. Caps on premium shifts per officer per month and rotation policies are operational controls, not welfare gestures.

Related in this series

• Overview: What Does a Security Agency in Singapore Actually Need From an ERP?
• licence eligibility gating
• geofenced attendance
• fatigue scoring and the guard app