Flag Public Holidays in Excel Data with Python

Daily sales, delivery times, call volumes and staffing figures all behave differently on public holidays, and a report that ignores them misleads in predictable ways: an average daily order count dragged down by Christmas, an SLA breach counted against a day the warehouse was legally closed, a forecast that expects a normal Monday on Easter Monday. Knowing which dates are holidays sounds trivial until the data covers several countries, holidays move every year (Easter), some apply only in certain regions (Bavaria has holidays Berlin does not), and a holiday that falls on a Saturday is often moved to the following Monday. The holidays package knows all of this for more than a hundred countries. This page uses it with pandas to flag holiday rows per country and region, add company closure days, count working days between dates, and compare averages with and without holidays. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install holidays pandas openpyxl numpy

shipments.xlsx holds daily shipments for five distribution centres in England, Scotland, Bavaria, Berlin and France from mid-December 2025 to mid-June 2026 — a period with Christmas, New Year, Easter, May holidays and several regional differences. deliveries.xlsx holds four delivery requests with requested and delivered dates, for counting working days. The examples were run with holidays 0.105.

Step 1 — Holidays for each country and region in the data

Build one holiday calendar per country-and-region combination that appears in the data. Regions are called subdivisions in the package:

import holidays
import pandas as pd

ship = pd.read_excel("shipments.xlsx")
years = sorted(ship["Date"].dt.year.unique())
places = ship[["Country", "Region"]].drop_duplicates()

calendars = {}
for country, region in places.itertuples(index=False):
    sub = region if isinstance(region, str) else None
    calendars[(country, sub)] = holidays.country_holidays(country, subdiv=sub, years=years)

for key in [("GB", "ENG"), ("GB", "SCT"), ("DE", "BY"), ("DE", "BE")]:
    hols = calendars[key]
    print(key, len(hols), "holidays, e.g.", sorted({n for d, n in hols.items() if d.month in (1, 3)})[:3])

holidays.country_holidays(country, subdiv=..., years=...) returns a dictionary-like object of dates and names for that place and those years. England and Scotland differ — Scotland has 2 January and St Andrew's Day, England has Easter Monday but Scotland does not — and Bavaria has Epiphany and Corpus Christi, which Berlin does not, while Berlin has International Women's Day on 8 March. Building calendars from the combinations actually present in the data means a new site in a new country only needs its country and region codes filled in. Keep the package up to date: governments announce one-off holidays, and the version used here already lists a one-off Scottish holiday on 15 June 2026 that an older version would miss — worth reviewing whenever the package is upgraded.

Step 2 — Flag each row with its holiday

With the calendars built, each row looks up its own date in its own place's calendar:

def holiday_name(row):
    sub = row["Region"] if isinstance(row["Region"], str) else None
    return calendars[(row["Country"], sub)].get(row["Date"].date())

ship["Holiday"] = ship.apply(holiday_name, axis=1)
ship["Is holiday"] = ship["Holiday"].notna()
ship["Is weekend"] = ship["Date"].dt.dayofweek >= 5
print(ship.loc[ship["Is holiday"], ["Date", "Site", "Holiday"]].drop_duplicates(["Date", "Holiday"]).head(10).to_string(index=False))
print(ship[ship["Date"] == "2026-01-06"][["Site", "Is holiday", "Holiday"]].to_string(index=False))

Each row now carries the holiday's name, or nothing, plus boolean flags for holidays and weekends. Checking a single date shows the regional effect: 6 January is a holiday in Munich and nowhere else in the data. The name is worth keeping alongside the flag — "why is this day low?" is answered by "Corpus Christi" far better than by True. For large tables, apply the lookup per unique (date, country, region) combination and merge the result back, rather than row by row; the logic is the same.

Step 3 — Substitute days and company closures

When a holiday falls on a weekend, many countries give a substitute weekday — Boxing Day on a Saturday becomes a holiday on Monday 28 December. The package includes these "observed" days where the law provides them; company-specific closures can be added to a calendar by hand:

eng = calendars[("GB", "ENG")]
print({d.isoformat(): n for d, n in sorted(eng.items()) if d.month == 12})

company = holidays.country_holidays("GB", subdiv="ENG", years=years)
company.update({pd.Timestamp("2025-12-24").date(): "Company closure", pd.Timestamp("2025-12-31").date(): "Company closure"})
leeds = ship["Site"] == "Leeds DC"
ship.loc[leeds, "Closed"] = ship.loc[leeds, "Date"].dt.date.map(lambda d: company.get(d))
print(ship.loc[leeds & ship["Closed"].notna(), ["Date", "Closed"]].head(6).to_string(index=False))

England's December entries show the effect: in 2025 Christmas Day and Boxing Day fall on a Thursday and Friday, while in 2026 Boxing Day is a Saturday and the package adds "Boxing Day (observed)" on Monday 28 December automatically. update adds company closure days — Christmas Eve and New Year's Eve here — to a copy of the public calendar, so the Leeds site's "closed" flag combines both. Keeping company days in a separate, explicit list (or a sheet maintained by HR) makes them easy to review each year; mixing them invisibly into the public calendar would make it hard to tell later why a date was excluded.

Step 4 — Working days between two dates, skipping holidays

Delivery times, SLAs and payment terms are usually counted in working days. NumPy's business-day functions accept a holiday list:

import numpy as np

deliv = pd.read_excel("deliveries.xlsx").merge(ship[["Site", "Country", "Region"]].drop_duplicates(), on="Site")

def working_days(row):
    sub = row["Region"] if isinstance(row["Region"], str) else None
    hols = holidays.country_holidays(row["Country"], subdiv=sub, years=[2026])
    hol_dates = np.array(list(hols.keys()), dtype="datetime64[D]")
    return int(np.busday_count(row["Requested"].date(), row["Delivered"].date(), holidays=hol_dates))

deliv["Calendar days"] = (deliv["Delivered"] - deliv["Requested"]).dt.days
deliv["Working days"] = deliv.apply(working_days, axis=1)
print(deliv[["Site", "Requested", "Delivered", "Calendar days", "Working days"]].to_string(index=False))

np.busday_count(start, end, holidays=...) counts weekdays from the start date up to (not including) the end date, skipping the listed holidays. The Leeds request from 31 March to 8 April spans Good Friday and Easter Monday, so eight calendar days are only four working days; the Munich order over the same period loses Good Friday and Easter Monday too, while the Berlin delivery in late May loses Whit Monday. The Leeds request from 23 to 30 December 2026 shows the substitute day at work: seven calendar days, but only three working days once Christmas Day and the observed Boxing Day are skipped. A delivery promised "within five working days" can be checked correctly only this way. Business days without holidays are covered in Calculate Business Days Between Excel Dates with pandas.

Step 5 — Holidays and daily averages

The most common reporting error is averaging across holidays. Compare a site's average weekday shipments with and without holidays:

weekdays = ship[~ship["Is weekend"]]
summary = weekdays.groupby("Site").agg(
    all_weekdays=("Shipments", "mean"),
    working_days_only=("Shipments", lambda s: s[~weekdays.loc[s.index, "Is holiday"]].mean()),
    holiday_weekdays=("Is holiday", "sum"),
).round(1)
print(summary.to_string())
ship.to_excel("shipments_flagged.xlsx", index=False)

In this synthetic data, holidays were not simulated as quiet days, so the averages barely differ — which is itself the check: real data would show a clear gap, and the holiday_weekdays column shows how many weekday holidays each site had in the period, from Munich and Berlin with more regional holidays to France. With real shipments that drop on holidays, the "working days only" average is the one to use for capacity planning and forecasting, and the holiday rows deserve their own line in the report. The flagged table is written back to Excel, ready for pivots that slice by holiday.

Common pitfalls

• One national calendar for all sites. Regional holidays differ; use subdivisions.
• Hard-coded holiday dates. Easter and others move every year; generate them.
• Forgetting substitute days. A weekend holiday often moves to Monday; the package handles it.
• Company closures mixed in silently. Keep them as a separate, reviewable list.
• Averages that include holidays. Report working-day averages separately.

Conclusion

To make Excel data holiday-aware, build a calendar with the holidays package for each country and region in the data, look up every row's date in its own calendar to add a holiday flag and name, and add company closure days from an explicit list. Use the same holiday lists with np.busday_count to count working days for delivery times and SLAs, and calculate daily averages on working days only, with holiday rows reported separately.

Frequently asked questions

How do I add public holidays to a pandas DataFrame? Create a calendar with the country_holidays function of the holidays package, passing the country, the region as subdiv and the years, then map each date to the calendar's get method, which returns the holiday name or None.

How do I handle regional holidays such as German states? Pass the region code as subdiv, for example "BY" for Bavaria, and build a separate calendar for each country and region combination in the data.

Does the holidays package include substitute days? Yes, where the country's rules provide them, such as a Monday holiday when Christmas falls on a weekend in England.

How do I count working days excluding holidays? Use numpy.busday_count with a holidays argument containing the holiday dates for the relevant country and region.

Related

• Up one level: Working with Dates and Times in Excel Data — dates in Excel data.
• Highlight Weekends and Holidays in a Date Column — shading the same days in Excel.
• Calculate Working Hours Between Two Timestamps — holidays at hour level.
• Build a Support Ticket SLA Report — working-time SLAs in practice.