From Excel to CP-SAT: Migrating a Manual Roster

The Excel starting point

Most planners start with Excel. The layout is almost always the same: employees as rows, days as columns, shift codes typed into cells. Color coding distinguishes functions (yellow for security, blue for logistics, green for check-in). Conditional formatting highlights conflicts. Formulas count hours per employee, sometimes per week, sometimes per month.

It works. For 20-30 people, a skilled planner can build a monthly roster in 3-5 days. They know their team, they know the patterns, they scroll up and down and left and right until every cell is filled and every rule is satisfied. The spreadsheet is both the tool and the documentation.

Beyond 50 employees, it breaks. Not suddenly, but gradually. The planner misses a rest period violation buried in row 47. An uncovered slot on a Tuesday goes unnoticed because the column is off-screen. A fairness complaint from an employee reveals that one person has worked 5 more days than their colleague. The spreadsheet does not flag any of this. It only stores what the planner types.

Hidden constraints

An Excel roster contains far more rules than what appears in the cells. The planner follows implicit constraints that exist only in their head:

• "Jean always works mornings because he has school pickup at 15:30."
• "Never put Ahmed and Sophie on the same shift. They do not work well together."
• "The night shift needs at least one senior with 3+ years of experience."
• "Marc does not work Wednesdays. He never has. Everyone knows this."
• "During school holidays, Claire prefers afternoon shifts."

None of these rules are written down. They live in the planner's memory, built up over months or years of experience. When the planner is sick or on vacation, their replacement produces a schedule that is technically correct but operationally wrong. Complaints follow.

This is the first problem a solver migration addresses: making implicit rules explicit. Every constraint must be written down, categorized, and prioritized. It is uncomfortable work. But once done, the scheduling logic becomes transferable, auditable, and independent of any single person.

Parsing the data

The first technical step is extracting structured data from the Excel files. A typical migration requires 5-6 data files:

Employee list

Name, contract type (full-time, part-time, student), target monthly hours, qualifications held, group membership. This is usually scattered across multiple sheets or even separate files. Consolidating it into a single structured format (JSON or database) is the foundation.

Shift definitions

Each shift code (e.g. A10-GS, N01-GS) maps to a start time, end time, break structure, and function. In the spreadsheet, this mapping lives in the planner's head or in a reference sheet that may be outdated. Extracting it forces a clean, current definition of every shift.

Daily needs

How many people are needed per function per day. Monday might need 5 security agents and 3 logistics handlers, while Sunday needs 2 and 1. These needs drive the solver's coverage objective. In Excel, they are implicit in the planner's target layout.

Constraints

Absences (holidays, vacations, fixed days off), preferences, and special rules. Each becomes a typed record: employee X, constraint type HOLIDAY, dates March 15-22. The hidden constraints from the planner's head become explicit entries in this file.

# Example data files for the solver:
01_employees.json      # 150 employees with qualifications and contracts
02_shifts.json         # 50 shift definitions with times and breaks
03_daily_needs.json    # Staffing requirements per function per day
04_constraints.json    # Absences, preferences, fixed assignments
05_groups.json         # Functional groups and their members

This parsing step typically takes 1-2 weeks for a medium-sized operation. The data exists, but it is scattered, inconsistent, and incomplete. The effort is not in writing code. It is in having conversations with the planner to extract and validate every piece of information.

Building the model

Each rule from the Excel world translates into a CP-SAT constraint. The mapping is surprisingly direct:

Excel rule	CP-SAT constraint
One shift code per cell (per employee per day)	sum(assign[e, d, s] for s) <= 1
Red cells = conflict (rest period violation)	Toxic pairs: assign[e, d, s1] + assign[e, d+1, s2] <= 1
Color-coded functions (yellow = security)	Qualification enforcement: variable only created for valid employee-shift pairs
"Jean always works mornings"	Constraint: restrict assign[Jean, d, s] to morning shifts only
"Never Ahmed + Sophie on same shift"	For each day d, each shift s: assign[Ahmed, d, s] + assign[Sophie, d, s] <= 1
Target hours per employee	Soft objective: penalize deviation from target_minutes[e]
Fair distribution across team	Equity objective: minimize max_days - min_days per group

The key insight: the Excel planner already solves a constraint satisfaction problem. They just do it manually, one cell at a time, using intuition instead of search. The solver does the same thing, but it explores the entire solution space systematically.

150 employees × 30 days × 50 shifts = 225,000 possible assignments. No human evaluates all of those. The planner evaluates maybe a few hundred before settling on a solution. The solver evaluates millions.

First solver run

The planner runs the solver for the first time, imports the result into a familiar grid layout, and compares. The reactions are predictable:

• "Why did it put Marc on Wednesday? He never works Wednesdays." (Missing constraint: Marc has a fixed day off on Wednesdays.)
• "It gave the night shift to a junior who started last month." (Missing constraint: night shifts require minimum seniority.)
• "There are three people in logistics on Thursday but we only need two." (Data error: daily needs file says 3, should be 2.)
• "The schedule looks fine but it feels wrong." (Implicit preference not yet captured.)

Each of these reveals a gap between the model and reality. The planner identifies the problem, the constraint is added or the data is corrected, and the solver runs again. This feedback loop is the core of the migration process.

After 3-4 iterations, the model typically captures 95% of the rules. The remaining 5% are edge cases and soft preferences that the planner handles with small manual adjustments after the solver produces its output. This is expected and acceptable. The goal is not to eliminate the planner. It is to eliminate the manual construction of the base schedule.

Comparing results

A side-by-side comparison of manual versus solver schedule typically reveals measurable differences:

Coverage

The solver achieves 100% coverage of declared needs. The manual schedule often has 2-5 uncovered slots that the planner resolves on the day itself through phone calls and last-minute swaps. The solver eliminates this reactive firefighting.

Rest period compliance

The solver produces zero rest period violations (11-hour rule). Manual schedules frequently contain 3-8 violations per month, discovered only when an employee complains or when an audit catches them.

Equity

The solver distributes workload within a 1-2 day gap per group. Manual schedules show gaps of 3-5 days between the most and least busy employees. Over several months, this gap compounds and becomes a source of tension.

Total hours

The solver uses the same or fewer total working hours to achieve better coverage. It finds combinations the planner would not consider because they require moving 4-5 employees simultaneously, something that is impractical to do manually but trivial for a search algorithm.

The transition

The migration is not a big bang. It follows a deliberate, low-risk sequence:

• Month 1-2: Run the solver in parallel with manual planning. The planner builds the schedule as usual, then compares it with the solver output. Discrepancies reveal missing constraints.
• Month 3: The planner starts from the solver output instead of a blank grid. They review, adjust, and approve. The manual schedule becomes the fallback.
• Month 4+: The solver output is the primary schedule. The planner focuses on exceptions, last-minute changes, and employee requests. Manual construction is no longer needed.

The planner must trust the output before they rely on it. Trust comes from understanding, and understanding comes from comparing. Skipping the parallel phase is tempting but counterproductive. Every discrepancy found during the parallel phase is a constraint that would have been missed in production.

What changes

The planner's role shifts fundamentally. The time allocation changes from execution to supervision:

Task	Before (Excel)	After (solver)
Building the base schedule	3-5 days	Minutes (solver run + review)
Checking rest period compliance	2-4 hours (manual scan)	0 (guaranteed by constraints)
Balancing workload equity	1-2 hours (trial and error)	0 (optimized by objective)
Handling employee complaints about fairness	Frequent	Rare (data-backed distribution)
Managing last-minute changes	Ad hoc, stressful	Re-run solver with updated constraints
Onboarding a new planner	Months of shadowing	Days (rules are in the model)

The planner does not disappear. They become more strategic. Instead of placing shift codes in cells for 3-5 days, they spend their time on what actually requires human judgment: handling unusual requests, adjusting for unexpected events, and improving the scheduling rules over time.

The model becomes the institutional memory. When the planner changes, the rules stay. When an employee asks "why was I assigned this shift?", the answer is in the constraint log, not in someone's head.