Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Create a Shift Calendar with Rotating Patterns

Generate a shift rota for teams on a rotating pattern — such as four teams on a 2-2-3 day and night cycle — with pandas: store each team's pattern as a repeating sequence anchored to a start date, expand it to any date range, prove every day has exactly one team on days and one on nights, total hours per team per month, mark public holidays, and write a colour-coded calendar grid to Excel.
Dates & timesReport layouts & templates

Round-the-clock operations — a warehouse, a control room, a care home, a production line — run on rotating shift patterns. Four teams cover days and nights between them, each working a fixed sequence that repeats every few weeks, and the rota for any future date follows from the pattern and one starting date. Rotas are nevertheless often kept in spreadsheets filled in by hand, month by month, with the familiar results: a day where two teams are on nights and nobody is on days, or a team that silently works more hours than the others for a quarter. Generating the rota from the pattern removes both problems. This page stores a common pattern — the 2-2-3 or Panama rotation, on 12-hour shifts — as a sequence, expands it to a quarter with pandas, checks coverage for every day and shift, totals the hours per team, marks public holidays, and writes a colour-coded calendar to Excel. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

Bash
pip install pandas openpyxl holidays

The pattern used here has four teams, A to D, working 12-hour day shifts (D) or night shifts (N) or resting (–). Each team follows the same 28-day sequence, offset by a week from the next team, so that every day exactly one team is on days and one on nights.

Step 1 — The pattern as data

Python
import pandas as pd

# 2-2-3 "Panama" rotation over 28 days: two on, two off, three on, then the same on nights
DAYS_HALF   = "DD--DDD--DD---"
NIGHTS_HALF = "NN--NNN--NN---"
CYCLE = DAYS_HALF + NIGHTS_HALF                       # 28 characters, one per day
assert len(CYCLE) == 28

# each team starts the cycle on a different day; the offsets are chosen so the teams interlock
OFFSETS = {"A": 0, "B": 14, "C": 7, "D": 21}
ANCHOR = pd.Timestamp("2026-01-05")                  # day 0 of the cycle for team A
print("cycle:", CYCLE)
print({team: CYCLE[off:] + CYCLE[:off] for team, off in OFFSETS.items()})

The whole rota is defined by three things: the cycle of shift codes, one character per day; each team's offset into that cycle; and an anchor date on which team A is at position zero. The 2-2-3 pattern works two days, rests two, works three, rests two, works two, rests three — fourteen days covering seven shifts — first on days and then on nights. Shifting the four teams by 0, 7, 14 and 21 days interlocks them so the gaps in one team's pattern are filled by another. Keeping the pattern as a short string means it can be read, reviewed and changed by anyone, and a different pattern — four on, four off, or a three-team rotation — is just a different string and set of offsets.

The first two weeks of the cycle for each team In the first two weeks teams A and B work days 0, 1, 4, 5, 6, 9 and 10, A on days and B on nights. Teams C and D work the other seven days, swapping between days and nights, so every day has one team on days and one on nights. A B C D E F G H I J K L M N O 1 Team 0 1 2 3 4 5 6 7 8 9 10 11 12 13 2 A D D – – D D D – – D D – – – 3 B N N – – N N N – – N N – – – 4 C – – D D – – – N N – – N N N 5 D – – N N – – – D D – – D D D A team's shift on any date Count the days from the anchor date to the date, add the team's offset, take the remainder after dividing by the 28-day cycle length, and look up that position in the cycle string to get D, N or a rest day. Date e.g. 1 Oct 2026 − anchor days since 5 Jan 2026 + team offset A 0, B 14, C 7, D 21 mod 28 position in the cycle Cycle[position] D, N or rest

Step 2 — Expand it to any date range

Python
def rota(start, end):
    dates = pd.date_range(start, end, freq="D")
    rows = {}
    for team, off in OFFSETS.items():
        pos = ((dates - ANCHOR).days + off) % len(CYCLE)
        rows[team] = [CYCLE[p] for p in pos]
    return pd.DataFrame(rows, index=dates)

q4 = rota("2026-10-01", "2026-12-31")
print(q4.head(7).rename(index=lambda d: d.strftime("%a %d %b")).T.to_string())

For any date, a team's position in the cycle is the number of days since the anchor, plus the team's offset, modulo the cycle length. That one line of arithmetic gives the rota for any period, past or future, with no state carried from one month to the next. The modulo also handles dates before the anchor correctly, because Python's % always returns a non-negative result. Producing the fourth quarter of 2026 is a call with two dates; producing all of next year is the same call with different dates. The first week of October shows the pattern at work: on Thursday 1 October team D is on days and C on nights, and from Friday the 2nd teams B and A take over for the weekend.

Step 3 — Prove the coverage and compare the hours

Python
coverage = pd.DataFrame({"Day teams": (q4 == "D").sum(axis=1), "Night teams": (q4 == "N").sum(axis=1)})
problems = coverage[(coverage["Day teams"] != 1) | (coverage["Night teams"] != 1)]
print("days checked:", len(coverage), "| days with a coverage problem:", len(problems))

hours = (q4 != "-").astype(int).mul(12).groupby(q4.index.to_period("M")).sum()
hours.loc["Total"] = hours.sum()
print(hours.to_string())

The check that matters most is coverage: for every date, exactly one team on days and exactly one on nights. Counting the codes across each row and listing any day where either count is not one gives a definite answer for the whole quarter — zero problem days here. Run the same check after any manual swap is entered into the rota, and a swap that leaves a night uncovered is caught before the night arrives. Hours per team per month, at twelve hours a shift, show whether the pattern is fair over the period. Over a full 28-day cycle every team works the same fourteen shifts, but calendar months do not line up with the cycle. In October and December teams A and B work sixteen shifts (192 hours) against fifteen for C and D, and over the quarter A and B end up two shifts ahead — 564 hours against 540. The difference evens out over the following cycles, but it is exactly the kind of imbalance that causes complaints if nobody can show where it comes from; the hours table shows it, and a long-run total over a year shows it disappear.

Two checks on every rota The coverage check counts, for each date, the teams on days and the teams on nights and requires exactly one of each. The hours check totals twelve hours per worked shift by team and month to show whether the load is shared fairly. Coverage, per day exactly one team on days exactly one team on nights list any day that fails rerun after every swap Hours, per team and month 12 h per worked shift months do not match the cycle equal over a full cycle compare quarterly totals

Step 4 — The calendar in Excel, with holidays

Python
import holidays
from openpyxl import load_workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Alignment, Font, PatternFill

bank = holidays.country_holidays("GB", subdiv="ENG", years=2026)
grid = q4.T
grid.columns = [d.strftime("%d %b") for d in q4.index]
path = "shift_rota_2026-Q4.xlsx"
with pd.ExcelWriter(path) as xw:
    grid.to_excel(xw, sheet_name="Rota", startrow=1)
    hours.to_excel(xw, sheet_name="Hours")
    coverage.rename(index=lambda d: d.date()).to_excel(xw, sheet_name="Coverage check")

wb = load_workbook(path)
ws = wb["Rota"]
for j, d in enumerate(q4.index, start=2):
    ws.cell(1, j, d.strftime("%a")).alignment = Alignment(horizontal="center")
    if d.date() in bank:
        ws.cell(1, j, "BH").font = Font(bold=True, color="C00000")
    ws.column_dimensions[ws.cell(2, j).column_letter].width = 6.5
area = f"B3:{ws.cell(2, ws.max_column).column_letter}6"
for code, colour in [("D", "FFF2CC"), ("N", "D9E1F2")]:
    ws.conditional_formatting.add(area, CellIsRule(operator="equal", formula=[f'"{code}"'], fill=PatternFill("solid", bgColor=colour)))
for row in ws.iter_rows(min_row=3, min_col=2):
    for cell in row:
        cell.alignment = Alignment(horizontal="center")
ws.freeze_panes = "B3"
wb.save(path)
print("bank holidays in the quarter:", [(d, n) for d, n in sorted(bank.items()) if d >= q4.index[0].date()])

The calendar puts teams down the side and dates across, with the day of the week above each date, so a team leader can read a whole quarter on one sheet. Day shifts are pale yellow and night shifts pale blue, applied with conditional formatting so that a manual swap typed into the sheet changes colour automatically. Public holidays are marked BH in red above the date, which matters because holiday shifts are usually paid at a higher rate and need to be counted. The hours and coverage tables go on their own sheets, giving the manager the evidence behind the rota. The quarter includes Christmas Day on Friday 25 December, Boxing Day on the Saturday and the substitute bank holiday on Monday 28 December, and the rota shows at a glance which teams work them — something worth agreeing well in advance.

Common pitfalls

  • Rotas typed by hand. Generate them from the pattern and an anchor date.
  • No coverage check. Count teams per shift per day after every change.
  • Expecting equal monthly hours. Months do not align with the cycle; compare over full cycles.
  • Losing the anchor. Store the anchor date with the pattern; without it the rota cannot be reproduced.
  • Hard-coded colours. Use conditional formatting so swapped shifts recolour themselves.

Conclusion

A rotating shift rota is a cycle of shift codes, an offset per team and an anchor date. A team's shift on any day is the cycle at position (days since anchor + offset) mod cycle length, which expands to any date range in one step. Check that every day has exactly one team on each shift, total the hours per team, mark the public holidays and write a colour-coded calendar whose formatting follows any later swaps.

Frequently asked questions

How do I generate a rotating shift pattern in Python? Store the pattern as a sequence of codes, give each team an offset, and look up (date − anchor).days + offset modulo the cycle length for every date.

What is the 2-2-3 shift pattern? A 14-day sequence of two days on, two off, three on, two off, two on and three off, usually run on 12-hour shifts and alternated between days and nights.

How do I check a rota covers every shift? Count the teams on each shift for each date and list any date where the count is not exactly one.

Why do teams work different hours in a month on the same pattern? Calendar months do not line up with the rotation cycle; over a full cycle every team works the same number of shifts.

More on dates & times

More on dates & times →