Build a Date Table for Excel Reports with pandas

Generate a calendar table — one row per day with year, quarter, month, ISO week, weekday, fiscal year, public holidays and working-day numbers — and use it to fill days with no sales, report by fiscal period, find the last working day of each month, and write it as an Excel table that lookups, pivots and Power Pivot can use.

Dates & times Report layouts & templates

Sales data only has rows for days on which something was sold. Reports, though, need every day: a daily chart with gaps in it lies about the trend, a month with no sales on a Sunday should still show Sunday, and "working days so far this month" needs a calendar that knows about weekends and bank holidays. The standard answer in reporting tools is a date table — one row per day, with every attribute a report might group or filter by already computed: year, quarter, month name, ISO week, fiscal period, weekend and holiday flags. pandas builds one in a few lines, and the same table can be written into the workbook for lookups, pivot tables and Power Pivot. This page belongs to Working with Dates and Times in Excel Data.

Prerequisites

Bash

pip install pandas openpyxl xlsxwriter

daily_sales.xlsx has sales for ten days at the end of March and the first half of April 2026 — with gaps for weekends, the Easter holidays (Good Friday 3 April and Easter Monday 6 April are bank holidays in England in 2026) and one working day, 8 April, with no sales.

Step 1 — One row per day

Python

import pandas as pd

dates = pd.DataFrame({"Date": pd.date_range("2026-01-01", "2026-12-31", freq="D")})
d = dates["Date"]
dates["Year"] = d.dt.year
dates["Quarter"] = "Q" + d.dt.quarter.astype(str)
dates["Month"] = d.dt.month
dates["MonthName"] = d.dt.strftime("%b")
dates["YearMonth"] = d.dt.strftime("%Y-%m")
dates["Weekday"] = d.dt.strftime("%a")
dates["WeekdayNum"] = d.dt.weekday + 1                    # 1 = Monday
iso = d.dt.isocalendar()
dates["ISOYear"], dates["ISOWeek"] = iso["year"].astype(int), iso["week"].astype(int)
print(dates.head(5).to_string(index=False))
print(len(dates), "days")

date_range with daily frequency gives every date, gaps included. Each attribute is a vectorised .dt accessor. Two details are easy to get wrong. ISO weeks belong to an ISO year that differs from the calendar year around New Year — 1 January 2026 is a Thursday in ISO week 1 of 2026, but 1 January 2027 falls in week 53 of 2026 — so keep ISOYear next to ISOWeek and group by both. And month names sort alphabetically in pivots unless a month number travels with them, which is why both columns are there.

Step 2 — Weekends, holidays and working days

Python

HOLIDAYS = pd.to_datetime(["2026-01-01", "2026-04-03", "2026-04-06", "2026-05-04", "2026-05-25",
                           "2026-08-31", "2026-12-25", "2026-12-28"])     # England & Wales, 2026
dates["IsWeekend"] = dates["WeekdayNum"] >= 6
dates["IsHoliday"] = dates["Date"].isin(HOLIDAYS)
dates["IsWorkingDay"] = ~dates["IsWeekend"] & ~dates["IsHoliday"]
dates["WorkingDayOfMonth"] = (dates["IsWorkingDay"].astype(int)
                              .groupby(dates["YearMonth"]).cumsum()
                              .where(dates["IsWorkingDay"]))
dates["WorkingDaysInMonth"] = dates.groupby("YearMonth")["IsWorkingDay"].transform("sum")
print(dates.loc[dates["Month"] == 4, ["Date", "Weekday", "IsHoliday", "IsWorkingDay", "WorkingDayOfMonth"]].head(9).to_string(index=False))

The holiday list is data, not logic: keep it in a sheet or file per country and load it, because rules such as "a Christmas Day that falls on Saturday moves to Monday" are exactly what an official list already resolves (25 December 2026 is a Friday, so the Boxing Day holiday moves to Monday 28th). WorkingDayOfMonth numbers each working day 1, 2, 3 within its month and leaves non-working days empty; April 2026 starts on a Wednesday, so 1 April is working day 1, Good Friday and Easter Monday are skipped, and Tuesday 7 April is only working day 3. Business-day arithmetic between two dates is covered in Calculate Business Days Between Excel Dates with pandas.

Step 3 — Fiscal year and period

Many organisations run an April–March year. With the start month as a parameter, fiscal year and period are arithmetic on the month:

Python

FY_START = 4                                           # April
shifted = (dates["Month"] - FY_START) % 12             # 0 = first fiscal month
dates["FiscalYear"] = "FY" + (dates["Year"] + (dates["Month"] >= FY_START).astype(int)).astype(str).str[-2:]
dates["FiscalPeriod"] = "P" + (shifted + 1).astype(str).str.zfill(2)
dates["FiscalQuarter"] = "FQ" + (shifted // 3 + 1).astype(str)
print(dates.loc[dates["Date"].isin(pd.to_datetime(["2026-03-31", "2026-04-01", "2026-12-31"])),
                ["Date", "FiscalYear", "FiscalPeriod", "FiscalQuarter"]].to_string(index=False))

31 March 2026 is the last day of FY26 (period 12); 1 April 2026 starts FY27 in period 1. Naming conventions differ — some firms call the April 2026 – March 2027 year "FY26" after its starting year — so make the label rule explicit and agree it with finance once. Grouping by these columns replaces date arithmetic scattered through report code; see Group Excel Data by Fiscal Year and ISO Week for the grouping side.

Step 4 — Fill the gaps in the sales data

Joining the sales to the date table gives a row for every day, with zero where nothing was sold:

Python

sales = pd.read_excel("daily_sales.xlsx")
daily = dates.merge(sales, on="Date", how="left").fillna({"Sales": 0})
window = daily[(daily["Date"] >= "2026-03-30") & (daily["Date"] <= "2026-04-14")]
print(window[["Date", "Weekday", "IsWorkingDay", "Sales"]].to_string(index=False))
print("average per working day in April:",
      round(daily.loc[(daily["Month"] == 4) & daily["IsWorkingDay"], "Sales"].mean(), 1))

The left merge keeps every date and fills missing sales with 0. Now the averages mean something: averaging only the days that had sales overstates performance, and averaging every calendar day understates it on weekends. Dividing by working days — a column the date table already has — answers the question managers actually ask. Filling missing dates without a full date table is shown in Fill Missing Dates in a Time Series from Excel with pandas.

Step 5 — Last working day and other flags

Flags precomputed in the date table turn awkward report logic into a filter:

Python

dates["IsLastWorkingDayOfMonth"] = dates["IsWorkingDay"] & (
    dates["WorkingDayOfMonth"] == dates["WorkingDaysInMonth"])
dates["IsMonthEnd"] = dates["Date"].dt.is_month_end
last_wd = dates.loc[dates["IsLastWorkingDayOfMonth"], ["YearMonth", "Date", "Weekday"]]
print(last_wd.head(6).to_string(index=False))

May 2026 ends on a Sunday, so its last working day is Friday 29 May; August ends on the bank-holiday Monday the 31st, so its last working day is Friday 28 August. Scheduling a report for "the last working day of the month" becomes a lookup in this table, as in Run a Report on the Last Business Day of the Month.

Step 6 — Write it as an Excel table

Written as a proper Excel table, the date table becomes a lookup source for XLOOKUP, a dimension for pivot tables, and a table Power Pivot can mark as the model's date table:

Python

with pd.ExcelWriter("date_table.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd") as xw:
    out = dates.copy()
    out.to_excel(xw, sheet_name="Dates", index=False, startrow=1, header=False)
    ws = xw.sheets["Dates"]
    ws.add_table(0, 0, len(out), len(out.columns) - 1,
                 {"name": "DateTable", "style": "Table Style Light 9",
                  "columns": [{"header": c} for c in out.columns]})
    ws.freeze_panes(1, 1)
    ws.set_column(0, 0, 12)
print(len(dates), "rows,", len(dates.columns), "columns written as table DateTable")

Writing the data without pandas' header (header=False, startrow=1) and letting add_table supply the headers produces a clean table named DateTable, which formulas can reference as DateTable[FiscalYear]. The approach is explained in Create an Excel Table with Python.

Performance notes

A date table is tiny — 365 rows a year, a few thousand for a decade — so building it is instant and joining it to millions of fact rows is a fast hash merge. Build it once per run, or once a year into a shared file that every report reads, rather than recomputing calendar logic inside each report. When several reports use it, keeping it in one file also guarantees they agree on what a working day or a fiscal period is, which is the real benefit: two reports that compute "working days in March" differently produce two different averages and a long meeting.

Common pitfalls

ISO week without ISO year. Late December and early January belong to the neighbouring ISO year; group by both.
Hard-coded holiday rules. Load official lists per country and year instead of computing them.
Inner joins. Merging sales to dates with how="inner" drops the empty days you wanted; use how="left" from the date table.
Month names sorting alphabetically. Keep the month number, or use an ordered categorical.
Fiscal labels. "FY27" can mean either end-year or start-year naming; document the rule.

Conclusion

A date table is one row per day with every attribute a report groups or filters by: calendar parts, ISO weeks with their ISO year, weekend and holiday flags, working-day counters, fiscal year and period. Build it with date_range and .dt accessors, left-join facts to it so missing days become zeros, use its flags for working-day averages and month-end logic, and write it as a named Excel table for lookups and pivots.

Frequently asked questions

How do I create a calendar table with every date in pandas? Use pd.DataFrame({"Date": pd.date_range(start, end, freq="D")}) and add columns with the .dt accessors, such as dt.year, dt.quarter, dt.strftime("%b") and dt.isocalendar().

How do I add a fiscal year that starts in April? Compute (month - 4) % 12 for the fiscal month index, and add one to the calendar year for months from April onwards. Agree with finance whether the fiscal year is named after its start or end year.

How do I show days with no sales as zero? Left-join the sales to the date table on the date column with how="left" from the date table, then fill the missing sales with 0.

How do I mark working days and bank holidays? Flag weekends with dt.weekday >= 5, flag holidays by checking membership in an official holiday list with isin, and define working days as neither.

Up one level: Working with Dates and Times in Excel Data — dates and times in spreadsheet data.
Group Excel Rows by Month and Quarter with pandas — calendar grouping.
Calculate Month-to-Date and Year-to-Date Totals with pandas — period-to-date figures built on a date table.
Build a Calendar Sheet in Excel with Python — a printable month view.

More on dates & times