Read a Calendar-Style Sheet into a Table

Calendars are a natural layout for people and an awkward one for analysis. A team's on-call rota, a meeting-room booking sheet or a leave tracker is often built as a printed-calendar grid: the month name at the top, weekday headings across, and for each week a row of day numbers with a row of entries underneath — "Ana", "Ben / Chloé", "Holiday". Two or three months sit one under another on the same sheet. Nobody can filter that by person or count days per person, and pandas reads it as a jumble of numbers and names. The information is all there, though: every entry sits under a day number, inside a month block. This page reads such a sheet with openpyxl, turns each block into dated rows, deals with the empty and padding cells calendars always have, and checks that every day of every month was captured. It belongs to Extracting Data from Messy Excel Sheets.

Prerequisites

pip install openpyxl pandas

oncall.xlsx holds an on-call rota for September and October 2026 on one sheet. Each month block has a bold title such as "September 2026", a row of weekday names, then for each week a row of day numbers and, underneath, the person on call — two names separated by "/" at weekends. As in printed calendars, the first and last weeks show the neighbouring month's days in grey italics to fill the grid, with no entries under them. Two days have no entry yet, and one has a note in brackets.

Step 1 — Find the month blocks

Each block starts with a title cell that parses as a month and year. Scan column A for those, so the code does not depend on how many rows each month takes:

from datetime import datetime
from openpyxl import load_workbook

wb = load_workbook("oncall.xlsx")
ws = wb["On-call"]

blocks = []
for r in range(1, ws.max_row + 1):
    value = ws.cell(r, 1).value
    if isinstance(value, str):
        try:
            first = datetime.strptime(value.strip(), "%B %Y")
        except ValueError:
            continue
        blocks.append({"title_row": r, "year": first.year, "month": first.month})
for b in blocks:
    print(b)

strptime with %B %Y turns "September 2026" into a date and rejects anything else, so person names, weekday headings and day numbers are ignored. If titles are written differently — "Sep-26", "2026-09" — add the formats to try. Locating blocks by their titles rather than by fixed row offsets is what keeps the reader working when a month needs six week rows instead of five, or when someone inserts a note row.

Step 2 — Pair day numbers with entries

Within a block, the row after the title holds weekday names; after that, rows alternate between day numbers and entries. Walk them in pairs until the next title or the end of the sheet:

def read_block(ws, block, next_title_row):
    records = []
    r = block["title_row"] + 2                          # skip title and weekday row
    while r < next_title_row:
        days = [ws.cell(r, c) for c in range(1, 8)]
        if not any(isinstance(d.value, int) for d in days):
            r += 1
            continue
        entries = [ws.cell(r + 1, c).value for c in range(1, 8)]
        for cell, entry in zip(days, entries):
            records.append({"year": block["year"], "month": block["month"], "row": r,
                            "day": cell.value, "padding": bool(cell.font and cell.font.italic), "entry": entry})
        r += 2
    return records

bounds = [b["title_row"] for b in blocks[1:]] + [ws.max_row + 1]
raw = [rec for b, nxt in zip(blocks, bounds) for rec in read_block(ws, b, nxt)]
print(len(raw), "day cells read;", sum(rec["padding"] for rec in raw), "are padding from neighbouring months")

A row counts as a day-number row when it contains integers; the row directly below holds the entries for those days. Blank spacer rows are skipped. Each record keeps the block's month and year, the day number and — importantly — whether the day number was formatted as padding. That formatting is the only thing distinguishing "1" for 1 October from "1" for the next month shown at the end of September's grid. If your calendar has no such formatting, use position instead: in the first week, day numbers larger than 7 belong to the previous month; in the last weeks, small numbers belong to the next.

Step 3 — Build real dates and tidy the entries

Drop the padding days, build a date for each remaining day, and split entries with several names into one row per person:

import pandas as pd
from datetime import date

days = pd.DataFrame([r for r in raw if not r["padding"]])
days["date"] = [date(y, m, d) for y, m, d in zip(days["year"], days["month"], days["day"])]
days["entry"] = days["entry"].fillna("").astype(str).str.strip()
days["note"] = days["entry"].str.extract(r"\(([^)]*)\)", expand=False)
days["people"] = days["entry"].str.replace(r"\s*\([^)]*\)", "", regex=True).str.split(r"\s*/\s*")

tidy = days.explode("people").rename(columns={"people": "person"})
tidy["person"] = tidy["person"].replace("", None)
tidy = tidy[["date", "person", "note"]].sort_values(["date", "person"], na_position="last").reset_index(drop=True)
print(tidy.head(8).to_string(index=False))
print(tidy[tidy["note"].notna()].to_string(index=False))

Each date is built from the block's year and month and the cell's day number, so it is a real date that sorts, filters and groups properly. Notes in brackets — "(swap with Ana)" — are extracted into their own column and removed from the name; weekend entries with two names separated by "/" become two rows with the same date via explode. Days with no entry keep a row with an empty person, so the gap is visible rather than missing. Splitting delimited cells into rows is covered in Split Delimited Cell Values into Rows with pandas.

Step 4 — Check every day was read

A calendar reader is easy to get subtly wrong — a skipped week, a padding day taken for a real one. Compare the dates read with the full set of dates in each month:

import calendar

expected = {date(b["year"], b["month"], d) for b in blocks
            for d in range(1, calendar.monthrange(b["year"], b["month"])[1] + 1)}
found = set(days["date"])
print("missing days:", sorted(expected - found) or "none", "| unexpected days:", sorted(found - expected) or "none")
print("days with no one on call:", sorted(d.isoformat() for d in days.loc[days["entry"] == "", "date"]))

Every day of September and October was read exactly once, and no padding day slipped in. The second line is the useful operational output: two days have nobody assigned, which the rota owner needs to fix. calendar.monthrange gives the number of days in each month, including February in leap years.

Step 5 — Use it like any table

Once the calendar is a table, the questions people actually ask are one-liners:

tidy["weekend"] = pd.to_datetime(tidy["date"]).dt.dayofweek >= 5
summary = tidy.dropna(subset=["person"]).groupby("person").agg(days=("date", "nunique"), weekend_days=("weekend", "sum"))
print(summary.to_string())
tidy.to_excel("oncall_table.xlsx", index=False)

How many days each person is on call, and how many of them fall at weekends, is a group-by — the question that is tedious to answer by counting cells in a calendar grid, and the basis for checking that a rota is fair. The tidy table is written to its own workbook, ready for filtering or loading into another system. If you need to go the other way — producing the calendar from a table — see Build a Calendar Sheet in Excel with Python.

Common pitfalls

• Fixed row offsets per month. Months need five or six week rows; locate blocks by their titles.
• Padding days read as real days. Use their formatting, or their position in the first and last weeks.
• Entries with several names in one row. Split them so each person can be counted.
• Losing empty days. Keep a row with no person so gaps are visible.
• No completeness check. Compare the dates read with every date in each month.

Conclusion

A calendar-style sheet is a set of month blocks, each with rows of day numbers and rows of entries beneath them. Find the blocks by parsing their titles, walk each block's rows in pairs to match entries to day numbers, skip padding days from neighbouring months using their formatting or position, and build real dates from the block's month and year. Split multi-name entries into one row per person, keep notes in their own column, and check that every date of every month was read before using the table.

Frequently asked questions

How do I convert a calendar layout in Excel to a list of dates? Locate each month block by its title, then read the rows of day numbers and the entry row beneath each, building a date from the block's year and month and each day number.

How do I skip the days from the previous and next month in a calendar grid? Use their formatting, such as grey or italic text, or their position: large day numbers in the first week and small ones in the last week belong to neighbouring months.

How do I handle several names in one calendar cell? Split the text on its separator, such as a slash, and use pandas explode so each name becomes its own row with the same date.

How can I check that every day was read from a calendar sheet? Build the set of all dates in each month with calendar.monthrange and compare it with the dates you read.

Related

• Up one level: Extracting Data from Messy Excel Sheets — getting data out of awkward layouts.
• Read a Sheet Where Records Run Across Columns — another sideways layout.
• Read Cell Colours and Font Styles as Data with openpyxl — formatting as information, as with the padding days.
• Generate a Staff Rota in Excel with Python — creating a rota rather than reading one.