Generate a Staff Rota in Excel with Python

Every week someone in a shop, a clinic or a support team sits down with last week's rota and a list of who cannot work when, and shuffles names around until every shift is covered. It takes an hour or two, the result is rarely fair — the same people get the weekends — and the rules that matter most, like not putting anyone on an early shift straight after a late one, are easy to break by accident. The problem is a classic small optimisation: choose who works which shift so that every shift has enough people and every rule holds, while respecting preferences as far as possible. SciPy's milp solver, which ships with SciPy, solves rotas of this size in a fraction of a second. This page models the rota, solves it, writes a colour-coded Excel sheet with coverage formulas, and checks every rule in the result. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas numpy scipy openpyxl

staff.xlsx lists six people with the most shifts each may work this week, the days they are unavailable, and a shift type they prefer to avoid. The rota is for the week of Monday 5 October 2026 with two shifts a day — Early (07:00–15:00) and Late (15:00–23:00). Weekdays need two people on each shift; at the weekend, two on Early and one on Late. milp uses the HiGHS solver bundled with SciPy 1.9 and later, so nothing else needs installing.

Step 1 — Load the inputs as sets and lookups

The model works with index positions, so turn the spreadsheet into lists and dictionaries first:

import numpy as np
import pandas as pd

staff = pd.read_excel("staff.xlsx").fillna("")
DAYS = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
SHIFTS = ["Early", "Late"]
NEED = {(d, s): (2 if d not in ("Sat", "Sun") or s == "Early" else 1) for d in DAYS for s in SHIFTS}

people = list(staff["Name"])
unavailable = {r.Name: {x.strip() for x in r.Unavailable.split(",") if x.strip()} for r in staff.itertuples()}
max_shifts = dict(zip(staff["Name"], staff["Max shifts"]))
avoid = dict(zip(staff["Name"], staff["Avoid"]))

idx = {(p, d, s): i for i, (p, d, s) in enumerate((p, d, s) for p in people for d in DAYS for s in SHIFTS)}
extra = {p: len(idx) + k for k, p in enumerate(people)}     # weekend shifts beyond the first, per person
NV = len(idx) + len(extra)
print(len(idx), "yes/no decisions;", sum(NEED.values()), "shifts to fill;", sum(max_shifts.values()), "shifts available")

Each decision is "does person p work shift s on day d?" — six people × seven days × two shifts gives 84 yes/no variables. The week needs 26 person-shifts and the team can offer at most 27, so there is very little slack; a manual rota with this little room for manoeuvre is exactly where rules get broken. Checking total supply against demand up front is worthwhile: if the team can offer fewer shifts than are needed, no rota exists and the solver will say "infeasible".

Step 2 — Write the rules as constraints

Every rule becomes a set of linear inequalities over the 0/1 variables. Collect them as rows of a matrix with lower and upper bounds:

from scipy.optimize import LinearConstraint

rows, lo, hi = [], [], []
def add(coeffs, lower, upper):
    row = np.zeros(NV)
    for key, c in coeffs.items():
        row[idx[key] if key in idx else extra[key]] = c
    rows.append(row); lo.append(lower); hi.append(upper)

for d in DAYS:
    for s in SHIFTS:                                            # exact cover for every shift
        add({(p, d, s): 1 for p in people}, NEED[d, s], NEED[d, s])
for p in people:
    for d in DAYS:                                              # at most one shift a day
        add({(p, d, s): 1 for s in SHIFTS}, 0, 1)
    add({(p, d, s): 1 for d in DAYS for s in SHIFTS}, 0, max_shifts[p])     # contracted maximum
    weekend = {(p, d, s): 1 for d in ("Sat", "Sun") for s in SHIFTS}
    add({**weekend, p: -1}, -np.inf, 1)                         # weekend shifts − extra ≤ 1 (soft rule)
    for today, tomorrow in zip(DAYS, DAYS[1:]):                 # no Late followed by Early
        add({(p, today, "Late"): 1, (p, tomorrow, "Early"): 1}, 0, 1)

constraints = LinearConstraint(np.array(rows), lo, hi)
print(len(rows), "constraints")

Exact cover (lower and upper bound both equal to the need) stops the solver over-staffing quiet shifts. "At most one shift a day" and the contracted maximum are simple sums. The weekend rule is written as a soft rule: weekend shifts minus an extra variable must be at most 1, so a second weekend shift is allowed but shows up in that person's extra variable, which the objective penalises. A hard limit of one weekend shift each was the first version of this model, and the solver reported it infeasible — rightly: the weekend has six slots, Ben cannot work weekends, and the five others can supply only five weekend shifts under that rule. Making the rule soft keeps the intent — spread weekends as evenly as possible — while letting the solver find a rota. The turnaround rule says a person's Late shift today and Early shift tomorrow cannot both be 1 — finishing at 23:00 and starting at 07:00 leaves only eight hours, below the eleven-hour daily rest many employment rules require. Unavailability is handled next, through variable bounds, which is simpler than extra constraints.

Step 3 — Preferences as the objective, then solve

Hard rules are constraints; wishes go into the objective. Give each assignment a cost — high for a shift type the person wants to avoid — and ask the solver for the cheapest rota that satisfies every rule:

from scipy.optimize import Bounds, milp

cost = np.ones(NV)                               # every shift costs 1 …
upper = np.ones(NV)
cost[len(idx):] = 5                              # each extra weekend shift costs 5
upper[len(idx):] = 2
for (p, d, s), i in idx.items():
    if avoid[p] == s:
        cost[i] += 10                            # … and a disliked shift costs much more
    if d in unavailable[p]:
        upper[i] = 0                             # unavailable: the variable can only be 0

res = milp(c=cost, constraints=constraints, integrality=np.ones(NV),
           bounds=Bounds(np.zeros(NV), upper))
print(res.status, res.message)
if res.status != 0:
    raise SystemExit("no rota satisfies the rules — relax a maximum or the cover")
x = np.round(res.x).astype(int)
assigned = {key: x[i] for key, i in idx.items()}
print("disliked shifts given:", sum(assigned[p, d, s] for (p, d, s) in idx if avoid[p] == s),
      "| people with two weekend shifts:", [p for p in people if x[extra[p]] > 0])

integrality=1 for every variable makes each one a whole number, and the bounds of 0 and 1 make it yes/no; unavailable slots get an upper bound of 0. Status 0 means an optimal rota was found. Because cover is exact, every feasible rota assigns the same 26 shifts, so minimising cost really means minimising disliked shifts and second weekend shifts. The weights encode priorities: a disliked shift (10) is worse than a second weekend shift (5). The solver finds a rota in which nobody works a shift type they asked to avoid and exactly one person works both weekend days — the unavoidable minimum. If the rules cannot all be met, res.status is 2 (infeasible); the usual fixes are relaxing a maximum or the weekend rule, and it pays to report which person-days are the bottleneck rather than just failing.

Step 4 — Write a colour-coded rota with coverage checks

The rota sheet has people down the side and days across, with E or L in each cell, a shift count per person, and coverage rows that count each day's Earlies and Lates with formulas:

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.styles import Alignment, Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Rota 5 Oct"
ws.append(["Name"] + DAYS + ["Shifts", "Max"])
for p in people:
    cells = ["E" if assigned[p, d, "Early"] else "L" if assigned[p, d, "Late"] else "" for d in DAYS]
    r = ws.max_row + 1
    ws.append([p] + cells + [f'=COUNTIF(B{r}:H{r},"?")', max_shifts[p]])
last = ws.max_row
for label, code in (("Early cover", "E"), ("Late cover", "L")):
    r = ws.max_row + 1
    ws.append([label] + [f'=COUNTIF({c}2:{c}{last},"{code}")' for c in "BCDEFGH"])
    ws.append([f"{label[:-6]} needed"] + [NEED[d, "Early" if code == "E" else "Late"] for d in DAYS])

fills = {"E": "FFF2CC", "L": "D9E1F2"}
for code, colour in fills.items():
    ws.conditional_formatting.add(f"B2:H{last}", CellIsRule(operator="equal", formula=[f'"{code}"'],
                                  fill=PatternFill("solid", fgColor=colour)))
for r in (last + 1, last + 3):
    ws.conditional_formatting.add(f"B{r}:H{r}", FormulaRule(formula=[f"B{r}<>B{r + 1}"],
                                  fill=PatternFill("solid", fgColor="FFC7CE")))
for row in ws.iter_rows(min_row=1, max_row=ws.max_row):
    for c in row[1:]:
        c.alignment = Alignment(horizontal="center")
for c in ws[1]:
    c.font = Font(bold=True)
ws.column_dimensions["A"].width = 14
wb.save("rota_2026-10-05.xlsx")
for p in people:
    print(f"{p:6}", " ".join(("E" if assigned[p, d, "Early"] else "L" if assigned[p, d, "Late"] else "·") for d in DAYS))

Earlies are shaded yellow and Lates blue by conditional formatting, so the pattern of the week is visible at a glance. The coverage rows are COUNTIF formulas comparing each day's count with the need underneath, and a rule turns any mismatch red. That matters after publication: when a manager swaps two shifts by hand, the sheet itself shows whether cover still holds. COUNTIF(..., "?") counts cells with exactly one character — any shift — for the per-person total.

Step 5 — Verify every rule in the result

Never trust a solver's output blindly — check it against the rules independently, from the written workbook:

from openpyxl import load_workbook

ws = load_workbook("rota_2026-10-05.xlsx")["Rota 5 Oct"]
grid = {ws.cell(r, 1).value: [ws.cell(r, c).value or "" for c in range(2, 9)] for r in range(2, 2 + len(people))}

problems = []
for d_i, d in enumerate(DAYS):
    for s, code in (("Early", "E"), ("Late", "L")):
        n = sum(row[d_i] == code for row in grid.values())
        if n != NEED[d, s]:
            problems.append(f"{d} {s}: {n} of {NEED[d, s]}")
for p, row in grid.items():
    if sum(bool(v) for v in row) > max_shifts[p]:
        problems.append(f"{p} over maximum")
    if any(row[DAYS.index(d)] for d in unavailable[p]):
        problems.append(f"{p} rostered when unavailable")
    if avoid[p] and any(v == avoid[p][0] for v in row):
        problems.append(f"{p} given a disliked {avoid[p]} shift")
    for i in range(6):
        if row[i] == "L" and row[i + 1] == "E":
            problems.append(f"{p}: Late {DAYS[i]} then Early {DAYS[i + 1]}")
print("rule violations:", problems or "none")
print("shifts per person:", {p: sum(bool(v) for v in row) for p, row in grid.items()})

The check reads the rota back from Excel — the version people will see — and tests cover, maximums, availability, preferences and turnarounds without using any of the solver's code. No violations, and the shift counts show how the 26 shifts were shared: Ben, Eva and Farid at their maximum of five, Ana and Chloé on four, and Dev on three, including both weekend days. Keep this check even if you trust the model: it catches mistakes in the model itself, such as an off-by-one in the turnaround pairs, and it can be rerun on a rota after manual edits.

Common pitfalls

• More shifts needed than people can work. Check supply against demand before solving; otherwise the model is infeasible.
• Preferences as hard rules. Put wishes — and fairness rules that may conflict, like weekends — in the objective so a rota still exists.
• Forgetting rest between shifts. Add the Late-then-Early constraint explicitly.
• Static cover numbers in Excel. Use COUNTIF formulas so manual swaps are checked too.
• Trusting the solver blindly. Verify the written rota against the rules independently.

Conclusion

A weekly rota is a small integer programme: one yes/no variable per person, day and shift; constraints for cover, one shift a day, contracted maximums, fair weekends, rest between shifts and availability; and an objective that penalises disliked shifts. SciPy's milp solves it in moments without extra software. Write the result as a colour-coded Excel grid with COUNTIF coverage rows that stay correct after manual swaps, and check every rule from the written file before the rota is published.

Frequently asked questions

How do I create a shift rota automatically with Python? Create a binary variable for each person, day and shift, express cover requirements and rules as linear constraints, give disliked shifts a higher cost, and solve with scipy.optimize.milp. Then write the assignments to an Excel grid.

Do I need a separate solver to schedule shifts in Python? No. SciPy 1.9 and later include milp, which uses the bundled HiGHS solver and handles rotas for teams of this size quickly.

How do I stop someone working an early shift straight after a late shift? For each person and each pair of consecutive days, add a constraint that the Late variable for the first day plus the Early variable for the next day is at most 1.

How can Excel show whether a rota still covers every shift? Add rows with COUNTIF formulas counting each day's E and L cells, compare them with the required numbers, and highlight mismatches with conditional formatting.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Build a Staff Utilisation Report from Timesheets — measuring the hours worked.
• Build a Calendar Sheet in Excel with Python — another grid laid out by day.
• Highlight an Entire Row Based on a Cell Value with openpyxl — formula-based conditional formatting.