Expand Date Ranges into One Row per Day with pandas

Many sheets describe things that last: a leave request from the 6th to the 10th, a contract from March to August, a hotel stay, a project assignment. Questions about them are usually asked per day or per month: how many people are off on Friday? How much of this contract's value belongs to April? Which week had the most rooms booked? The simplest way to answer all of them is to expand each range into one row per day it covers. After that, "per day" is a groupby on the date, and "per month" a groupby on the month — no interval arithmetic needed. This page is part of Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas numpy openpyxl

Two sheets, both with inclusive start and end dates. july_leave.xlsx has six leave requests in three teams. contracts.xlsx has four service contracts with a total value each, some of them spanning several months and one running into next year.

Step 1 — Expand with date_range and explode

For each row, build the list of days it covers, then explode the list so each day becomes its own row with the other columns copied:

import pandas as pd

leave = pd.read_excel("july_leave.xlsx", parse_dates=["Start", "End"])
leave["Day"] = [pd.date_range(s, e, freq="D") for s, e in zip(leave["Start"], leave["End"])]
daily = leave.explode("Day").drop(columns=["Start", "End"])
daily["Day"] = pd.to_datetime(daily["Day"])
print(len(leave), "requests ->", len(daily), "person-days")
print(daily.head(6).to_string(index=False))

pd.date_range(start, end) includes both ends, which matches inclusive end dates. After explode, the new column holds objects, so convert it back to a datetime column. Check the count straight away: the number of rows after expansion must equal the sum of each range's length in days, and a mismatch means a reversed or missing date somewhere:

expected = ((leave["End"] - leave["Start"]).dt.days + 1).sum()
assert len(daily) == expected, (len(daily), expected)
print("expected", expected)

Step 2 — Count per day

With one row per person per day, the daily headcount away is a group-by. Reindex over every day of the month so days with nobody away appear as zero, and pivot by team to see who is short:

july = pd.date_range("2026-07-01", "2026-07-31")
per_day = daily.groupby("Day").size().reindex(july, fill_value=0)
print(per_day["2026-07-06":"2026-07-15"].rename(lambda d: d.strftime("%d %b")).to_dict())
print("busiest day:", per_day.idxmax().date(), "with", per_day.max(), "away")

by_team = daily.pivot_table(index="Day", columns="Team", values="Employee", aggfunc="count", fill_value=0)
print(by_team.loc["2026-07-09":"2026-07-14"])

The busiest days are the 9th, 10th, 13th and 14th, with four people away. The team view shows where it hurts: on the 9th, both Ken and Claire from Sales are off, while Engineering never has more than two people away at once — the fact a manager needs before approving the next request. Find Overlapping Date Ranges with pandas lists which requests clash; the daily count shows how bad each day is.

Step 3 — Working days only

Leave is taken on working days; weekends inside a request are not days off. Use business days instead of calendar days, with public holidays excluded:

from pandas.tseries.offsets import CustomBusinessDay

holidays = ["2026-07-03"]                 # a company holiday
workdays = CustomBusinessDay(holidays=holidays)
leave["WorkDay"] = [pd.date_range(s, e, freq=workdays) for s, e in zip(leave["Start"], leave["End"])]
work = leave.explode("WorkDay").dropna(subset=["WorkDay"])
print(work.groupby("Employee").size().to_dict())

CustomBusinessDay skips Saturdays, Sundays and the listed holidays; freq="B" alone skips only weekends. A range that falls entirely on a weekend produces an empty list, which explode turns into a single row with NaN — hence the dropna. Ken's 1–17 July request is thirteen weekdays but only twelve days of leave, because the 3rd is a company holiday. Calculate Business Days Between Excel Dates with pandas covers holiday calendars in more depth.

Step 4 — Spread a value across the days: prorating

A contract worth 18,200 from 15 March to 14 June earns part of that value in each month. Expanding to days and giving each day an equal share turns "how much belongs to April" into a sum:

contracts = pd.read_excel("contracts.xlsx", parse_dates=["Start", "End"])
contracts["Days"] = (contracts["End"] - contracts["Start"]).dt.days + 1
contracts["Day"] = [pd.date_range(s, e) for s, e in zip(contracts["Start"], contracts["End"])]
per_day = contracts.explode("Day")
per_day["Day"] = pd.to_datetime(per_day["Day"])
per_day["Share"] = per_day["Value"] / per_day["Days"]

monthly = (per_day.assign(Month=per_day["Day"].dt.to_period("M"))
                  .pivot_table(index="Contract", columns="Month", values="Share", aggfunc="sum", fill_value=0)
                  .round(2))
print(monthly.iloc[:, :7])
print("check:", monthly.sum(axis=1).round(2).to_dict())

Every contract's monthly amounts add back up to its value — the check line proves it — so nothing is lost or double-counted by the expansion. The Arden contract earns only 17 days' worth in March, full months in April and May, and 14 days in June. The Dunmore contract runs into 2027, and its months beyond December fall into next year's columns. Daily proration is how straight-line revenue recognition and deferred-income schedules are usually calculated; if your policy uses equal monthly amounts instead of equal daily amounts, count months rather than days.

Step 5 — A faster expansion for large sheets

Building a Python list of dates per row is fine for thousands of rows. For hundreds of thousands, repeat each row by its length with NumPy and add a day counter, which avoids the per-row date_range calls:

import numpy as np

def expand_fast(df, start="Start", end="End"):
    lengths = (df[end] - df[start]).dt.days.to_numpy() + 1
    rows = df.loc[df.index.repeat(lengths)].copy()
    offsets = np.arange(lengths.sum()) - np.repeat(np.cumsum(lengths) - lengths, lengths)
    rows["Day"] = rows[start] + pd.to_timedelta(offsets, unit="D")
    return rows.reset_index(drop=True)

big = pd.concat([contracts[["Contract", "Start", "End", "Value"]]] * 5000, ignore_index=True)
import time
t = time.perf_counter()
fast = expand_fast(big)
print(f"{len(big):,} ranges -> {len(fast):,} day rows in {time.perf_counter() - t:.2f}s")

index.repeat(lengths) copies each row as many times as it has days, and the offset expression counts 0, 1, 2, … within each block, so the start date plus the offset is each day of the range. Twenty thousand contracts become over three million day rows in about a third of a second here. At that size, ask whether you need the day rows at all — for monthly totals, you can compute each range's overlap with each month directly — but for most reporting sheets, expanding is the clearest route.

Step 6 — Write the results to Excel

Put the daily headcount and the monthly recognition schedule into one workbook:

with pd.ExcelWriter("expanded.xlsx", engine="xlsxwriter", datetime_format="ddd dd mmm") as xw:
    per_day_counts = daily.groupby("Day").size().reindex(july, fill_value=0).rename("Away")
    per_day_counts.to_frame().to_excel(xw, sheet_name="Leave per day", index_label="Day")
    out = monthly.copy()
    out.columns = out.columns.astype(str)
    out.to_excel(xw, sheet_name="Recognition")
    money = xw.book.add_format({"num_format": "#,##0.00"})
    xw.sheets["Recognition"].set_column(1, len(out.columns), 11, money)
print("written")

When not to expand

Expanding is the most readable approach, but it is not the only one. For monthly totals over long ranges — ten-year leases, multi-year service contracts — the overlap of each range with each month can be computed directly: the number of days in max(start, month start) to min(end, month end), times the daily rate. That produces one row per contract per month instead of one per day, which matters when there are millions of ranges. For "how many are active on each date", a running sum of +1 at each start and −1 on the day after each end gives the same daily counts without materialising the day rows. Both are faster and both are easier to get subtly wrong; expand first, and keep the expanded version as a test for any optimised replacement.

Common pitfalls

• Exclusive end dates. date_range(start, end) includes the end; subtract a day first if the end date is exclusive, such as a check-out date.
• Forgetting to convert after explode. The exploded column is object; wrap it in pd.to_datetime before using .dt.
• Empty ranges. A weekend-only range with business days, or a reversed range, explodes to NaN rows; drop them and report reversed ranges.
• Counting rows instead of people. Two overlapping requests from one person make two rows for the same day; use nunique on the person if that matters.
• Memory. Long ranges times many rows grow quickly; estimate sum of lengths before expanding.

Conclusion

Build each row's list of days with pd.date_range — daily, business-day or a custom calendar with holidays — and explode it into one row per day, checking that the row count equals the total length of the ranges. Group by day for headcounts and occupancy, give each day an equal share of a value to prorate it across months, and switch to a NumPy repeat when the sheet is large.

Frequently asked questions

How do I create one row per day between a start and end date in pandas? Add a column with pd.date_range(start, end) for each row and call explode on it. Each day becomes its own row with the other columns copied; convert the new column back with pd.to_datetime.

How do I expand only business days? Use pd.date_range with freq="B" for weekdays, or with a CustomBusinessDay that lists your holidays. Drop the NaN rows that ranges containing no working days produce after explode.

How do I split a contract value across months? Expand each contract into days, give each day the value divided by the contract's length in days, and sum the shares by month with a pivot table. Check that the months add back up to the total value.

Is there a faster way than explode for large data? Yes: repeat each row by its length with df.locdf.index.repeat(lengths) and add a within-range day counter built with NumPy. It avoids calling date_range once per row.

Related

• Up one level: Working with Dates and Times in Excel Data — parsing, grouping and date arithmetic.
• Fill Missing Dates in a Time Series with pandas — the reindex step that adds zero days.
• Group Excel Rows by Month and Quarter with pandas — monthly totals from daily rows.
• Build a Cash Flow Forecast Report — spreading amounts over future periods.