Police Shift Schedule Maker

Minimum beats on every watch, rest between them, and weekends that don't all land on the same officers.

Patrol scheduling has a floor that most industries do not: a minimum number of units on the street at any hour, set by policy rather than by demand, and non-negotiable at 04:00 on a Tuesday. That floor is what you enter in the demand grid, and it is why patrol rosters are usually built backwards — from the minimum staffing level up — rather than from expected workload down.

The second thing that shapes a patrol roster is that officers are not interchangeable in the way the headcount suggests. A supervisor must be on every watch. Some assignments need a field training officer, a K9 handler, a bilingual officer, or someone certified for a specific post. Each of those is a separate demand stream here, and an officer with no skill row for a stream simply cannot be assigned to it — that is structural in the model, not a penalty you could outweigh with cost.

The loaded sample is a 21-day two-watch post on twelves with weekend fairness switched on, which is the closest small example to a patrol rotation. Weekend fairness matters more in policing than almost anywhere else on this site, because shift and weekend allocation is often contractual and is very often the thing that ends up in a grievance. The soft weekend penalty spreads Saturdays and Sundays across the roster instead of parking them on whoever the solver happened to reach first, and the per-person weekend counts on the results page let you show your working.

Two honest limits. This schedules officers to shifts — it does not do beat or sector assignment within a shift, and it does not model court appearances, training days or call-backs except insofar as you mark someone unavailable. And if your department allocates shifts by seniority bid rather than by optimization, use the f forced-cell marker to pin the bid results first and let the model fill only what is left; that is the honest way to combine a bidding system with a solver instead of pretending the solver replaces it.

Data

Download this sample (.xlsx)

Or drop your own workbook here — choose a file

Same sheet layout as the samples: shifts, demand all, supply all, skills, one grid per demand and per person, parameters. Download a sample first if you want the template.

Patrol scheduling questions

Can I run 12-hour watches and 10-hour watches in the same roster? Yes. Shift lengths come from the clock times you type, not from a setting, so a 10-hour overlap watch is just another row in the shifts sheet alongside your twelves.

How do I guarantee a supervisor on every shift? Make “supervisor” a demand with the concurrent flag and give only your sergeants a skill row for it. The supervisor then counts toward your minimum strength rather than being an extra body on top of it.

Our contract requires every other weekend off. Can it do that? Not as a hard guarantee — weekend fairness here is a penalty, not a constraint, so coverage will win if the two conflict. If every-other-weekend-off is genuinely inviolable, the honest tool for it is the Pitman rotation generator, which is a fixed pattern built for exactly that promise, and you accept the fixed pattern in exchange for the guarantee.

What about seniority bidding? Pin the bid outcomes with f in those officers' grid cells before solving. The model treats forced cells as hard and schedules everyone else around them, so the bid is honoured and the remainder is still optimized.

Does it handle overtime and call-back? No. It builds the planned roster; unplanned call-back is by definition what happens after the roster meets reality. The exported hours per officer are the right input to whatever tracks your overtime.

The same solver, other settings

Each of these loads a different worked example and is written for that setting, but they all run the same cross-day engine — if none of them matches yours, start from whichever is closest and replace the data.

correctional officer rosters — post-based staffing and the relief factor behind mandatory overtime
firefighter shift schedules — 24-hour shifts, platoons, and the rest rule that follows a 24
security guard posts — a small team covering one post, with weekend fairness switched on
the general 24/7 shift schedule maker — any round-the-clock operation; start here if none of the others fit

How this differs from the other two modes

The staff scheduling optimizer on the home page works in half-hour slots inside a single day: you say how many people you need at 08:00, at 08:30, at 09:00, and it invents shift blocks to match. That is the right model when demand rises and falls through the day — a call centre, a restaurant, a shop — and the wrong model here, because every one of its rules is scoped to one day column. A 19:00–07:00 night becomes two disconnected half-shifts, and no rest rule can span the midnight boundary.

This page replaces the time axis with a shift axis. Your shifts are named blocks with real clock times, and an end time earlier than the start time simply means the shift crosses midnight. Rest between two shifts then becomes arithmetic on absolute minutes across the whole horizon rather than a special case, which is what makes cross-day rules — minimum rest, maximum consecutive days, maximum consecutive nights, no day shift after a night — expressible at all.

The third mode, the shift rotation generator, does not optimize anything: it lays out a named pattern — DuPont, Panama, Pitman, 4-on-4-off — across four crews. Use it when you want a known, published rotation and you do not need the schedule to respond to who is qualified for what, who is on holiday, or which shifts need three people instead of two.

What the solver actually decides

Coverage, skills, availability, pinned assignments and contracted hours are hard — the model physically cannot assign someone to work they are not qualified for or not available for. Coverage shortfall, minimum-hours shortfall, weekend fairness and cross-person equity are soft: each carries a penalty rather than a prohibition. That is a deliberate choice. A roster tool that answers “infeasible” tells you nothing useful; this one always returns a roster and then names the compromises it had to make, in the warnings and the unfilled-posts table.

Why the continuity numbers are on the results page

A roster can be 100% covered and still be unliveable. Everyone in a pay grade earns the same rate and coverage pins the total hours, so rearranging who works when often costs the model literally nothing — the same ward solved twice with staff listed in a different order produced 21 different shifts at an identical objective. Left alone, the solver returns whichever scattered roster it reached first. So the model carries a small penalty on each new block of consecutive days worked, and another on flipping between days and nights, and the results page reports what came out: how many blocks, how long they are, how many are lone single days, and the tightest turnaround anyone actually got. Those are the numbers to argue with, not the objective value.

Common questions

Is it free? Yes — no account, no trial. Your workbook is read to solve the model and not stored.

How many people and days can it handle? Up to 40 people and 31 days per solve. The 28-day hospital ward sample builds roughly 4,300 variables and solves in well under a minute.

What if I want a shift to be someone specific? Put f in that person's grid cell. Forced cells are hard — the solver schedules around them.

Is minimum rest really 11 hours? Only by default. 11 hours is the EU Working Time Directive figure; the US has no federal equivalent and state rules vary, so it is a parameter (MinRestHours) rather than a constant. Set it to whatever your jurisdiction or contract says.