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

Calculate Business Days Between Excel Dates with pandas

Reproduce NETWORKDAYS, NETWORKDAYS.INTL and WORKDAY in Python: count working days between two date columns with numpy busday_count, include both ends like Excel, exclude holidays from a sheet, and add working days to a date.
Dates & timespandas

Service-level reports live and die by working days: how many business days a ticket stayed open, whether an invoice was paid within thirty working days, when a request is due if it arrived on a Friday. In Excel those are NETWORKDAYS and WORKDAY, filled down a column with a holiday range on another sheet. Moving the report to Python means reproducing them exactly — including the off-by-one rules that make a careless translation disagree with the spreadsheet it replaces. This guide, part of Working with Dates and Times in Excel Data, uses NumPy's business-day functions, which are vectorised and fast.

Counting working days across a weekend and a holiday From Thursday 24 December to Monday 4 January there are twelve calendar days. Weekends and the holidays on 25 December, 28 December and 1 January are excluded, leaving five working days when both ends are counted. A B C D E wk 1 Thu 24 Fri 25 Sat 26 Sun 27 Mon 28 wk 2 Tue 29 Wed 30 Thu 31 Fri 1 Sat 2 wk 3 Sun 3 Mon 4 teal = working day (5), pink = holiday, grey = weekend

Prerequisites

Bash
pip install pandas numpy openpyxl

A ticket log and a holiday list, written as a workbook with two sheets the way these reports are usually organised:

Python
import pandas as pd

tickets = pd.DataFrame({
    "Ticket": ["T-101", "T-102", "T-103", "T-104", "T-105"],
    "Opened": pd.to_datetime(["2026-09-07", "2026-09-11", "2026-12-24", "2026-09-12", None]),
    "Closed": pd.to_datetime(["2026-09-11", "2026-09-14", "2027-01-04", "2026-09-12", "2026-10-01"]),
})
holidays = pd.DataFrame({"Holiday": pd.to_datetime(["2026-12-25", "2026-12-28", "2027-01-01"])})

with pd.ExcelWriter("service_desk.xlsx") as writer:
    tickets.to_excel(writer, sheet_name="Tickets", index=False)
    holidays.to_excel(writer, sheet_name="Holidays", index=False)

Step 1 — Count business days with busday_count

np.busday_count(begin, end) counts Monday-to-Friday days in the half-open interval from begin up to but not including end. It works on arrays of datetime64[D], so a whole column is one call.

Python
import numpy as np

frame = pd.read_excel("service_desk.xlsx", sheet_name="Tickets", parse_dates=["Opened", "Closed"])
valid = frame[["Opened", "Closed"]].notna().all(axis=1)

begin = frame.loc[valid, "Opened"].to_numpy(dtype="datetime64[D]")
end = frame.loc[valid, "Closed"].to_numpy(dtype="datetime64[D]")
print(np.busday_count(begin, end))          # [4 1 7 0]

Converting to datetime64[D] drops the time of day, which is also what Excel does when a date cell carries a time. The valid mask matters because busday_count cannot handle NaT: ticket T-105 has no opening date and must be kept out of the call.

Step 2 — Match NETWORKDAYS by counting both ends

Excel's NETWORKDAYS(start, end) counts both the start and end dates when they are working days: Monday to Friday of the same week is 5, not 4. To match it, move the end one day later before calling busday_count.

Python
def networkdays(start: pd.Series, end: pd.Series, holidays=None) -> pd.Series:
    result = pd.Series(pd.NA, index=start.index, dtype="Int64")
    ok = start.notna() & end.notna()
    counts = np.busday_count(
        start[ok].to_numpy(dtype="datetime64[D]"),
        (end[ok] + pd.Timedelta(days=1)).to_numpy(dtype="datetime64[D]"),
        holidays=[] if holidays is None else holidays,
    )
    result[ok] = counts
    return result

frame["WorkingDays"] = networkdays(frame["Opened"], frame["Closed"])
print(frame[["Ticket", "Opened", "Closed", "WorkingDays"]])

Ticket T-101 now reads 5, as it would in the spreadsheet. T-104 opened and closed on a Saturday and correctly reads 0. T-105 stays <NA> instead of raising, which lets the report show it as "not measurable" rather than crashing the job.

Why busday_count and NETWORKDAYS differ by one numpy busday_count excludes the end date, so Monday to Friday gives four. Excel NETWORKDAYS includes both ends, giving five. Adding one day to the end before counting makes them agree. np.busday_count [begin, end) half-open Mon → Fri = 4 end date not counted Excel NETWORKDAYS [start, end] inclusive Mon → Fri = 5 both dates counted end + 1 day busday_count(start, end+1) Mon → Fri = 5 matches Excel check the convention before comparing with an existing spreadsheet

Step 3 — Exclude holidays from a sheet

Both functions take a holidays array. Read it from the workbook's holiday sheet so the finance team can maintain it without touching code, and convert it to datetime64[D] once.

Python
holiday_days = (
    pd.read_excel("service_desk.xlsx", sheet_name="Holidays")["Holiday"]
      .dropna()
      .to_numpy(dtype="datetime64[D]")
)

frame["WorkingDays"] = networkdays(frame["Opened"], frame["Closed"], holidays=holiday_days)
print(frame[["Ticket", "WorkingDays"]])
# T-103, open over Christmas and New Year, counts 5 instead of 8

Holidays that fall on a weekend are ignored automatically, so there is no need to filter the list. For a report spanning many years and countries, a maintained source is less error-prone than a typed list; the holidays Python package provides public holidays by country and year, and its dates convert the same way.

Step 4 — Use a custom weekend like NETWORKDAYS.INTL

Not every organisation works Monday to Friday. NETWORKDAYS.INTL takes a weekend code; NumPy takes a weekmask, either seven 0/1 characters starting on Monday or a list of day names.

Python
sunday_to_thursday = "Sun Mon Tue Wed Thu"
six_day_week = "1111110"                    # Monday to Saturday

start = np.datetime64("2026-09-06")         # a Sunday
end = np.datetime64("2026-09-12")           # the following Saturday
one_day = np.timedelta64(1, "D")
print(np.busday_count(start, end + one_day, weekmask=sunday_to_thursday))   # 5
print(np.busday_count(start, end + one_day, weekmask=six_day_week))         # 6

A different weekmask per row — offices in several countries in one report — is easiest handled by grouping on the country column and calling the function once per group with that country's mask and holiday list.

Step 5 — Add working days to a date like WORKDAY

Deadlines go the other way: a request received on a date is due a number of working days later. np.busday_offset(date, n) moves by n business days. The roll argument decides what happens when the start date itself is not a working day, and matching Excel's WORKDAY needs roll="backward" for positive offsets.

Python
def workday(start: pd.Series, days: int, holidays=None) -> pd.Series:
    result = pd.Series(pd.NaT, index=start.index, dtype="datetime64[ns]")
    ok = start.notna()
    shifted = np.busday_offset(
        start[ok].to_numpy(dtype="datetime64[D]"), days,
        roll="backward" if days >= 0 else "forward",
        holidays=[] if holidays is None else holidays,
    )
    result[ok] = pd.to_datetime(shifted)
    return result

frame["DueBy"] = workday(frame["Opened"], 3, holidays=holiday_days)
print(frame[["Ticket", "Opened", "DueBy"]])

For T-104, opened on Saturday 12 September, the due date is Wednesday 16 September — three working days counted from the weekend, the same answer WORKDAY gives. With the default roll="raise" NumPy would refuse the Saturday date entirely, and with roll="forward" it would first move to Monday and then add three days, landing a day late.

Due date for a ticket opened on a Saturday plus three working days With roll backward the Saturday is treated as the preceding Friday and three working days later is Wednesday, matching Excel WORKDAY. With roll forward it is treated as Monday and the result is Thursday, one day late. The default raise refuses the date. busday_offset("2026-09-12", 3) by roll setting roll="backward" Wed 16 Sep, as WORKDAY roll="forward" Thu 17 Sep, a day late roll="raise" ValueError weekend start dates are where translations go wrong

Step 6 — Flag SLA breaches and write the result

With counts and due dates as columns, the service report is a comparison and a write. Keep the working-day count as a number and let Excel format the dates.

Python
SLA_DAYS = 3
frame["Breached"] = frame["WorkingDays"].map(
    lambda days: pd.NA if pd.isna(days) else ("Yes" if days > SLA_DAYS else "No")
)

with pd.ExcelWriter("sla_report.xlsx", engine="openpyxl", datetime_format="yyyy-mm-dd") as writer:
    frame.to_excel(writer, sheet_name="SLA", index=False)

print(pd.read_excel("sla_report.xlsx")[["Ticket", "WorkingDays", "Breached"]])

Leaving Breached as missing for tickets without both dates keeps them out of breach counts and makes the data gap visible in the output. Formatting the date columns further is covered in Format Dates in Excel Cells with Python.

Step 7 — Build one business calendar and reuse it

Real reports rarely need a single count. They need working days open, a due date, working days overdue, and perhaps working days per month for capacity figures — all under the same weekend and holiday rules. A np.busdaycalendar bundles those rules into one object, validated once, that every business-day function accepts through busdaycal=. Changing the rules then means changing one line.

Python
calendar = np.busdaycalendar(weekmask="Mon Tue Wed Thu Fri", holidays=holiday_days)

opened = frame.loc[valid, "Opened"].to_numpy(dtype="datetime64[D]")
closed = frame.loc[valid, "Closed"].to_numpy(dtype="datetime64[D]")
due = np.busday_offset(opened, SLA_DAYS, roll="backward", busdaycal=calendar)

overdue = np.maximum(np.busday_count(due, closed, busdaycal=calendar), 0)
print(pd.DataFrame({"Ticket": frame.loc[valid, "Ticket"], "Due": due, "DaysOverdue": overdue}))

months = pd.period_range("2026-09", "2027-01", freq="M")
per_month = np.busday_count(months.start_time.to_numpy("datetime64[D]"),
                            (months.end_time + pd.Timedelta(days=1)).to_numpy("datetime64[D]"),
                            busdaycal=calendar)
print(dict(zip(months.astype(str), per_month)))

The overdue count uses the half-open convention on purpose: a ticket closed on its due date is zero days overdue, and one closed the next working day is one. The working days per month are a useful denominator for productivity figures, since a December with two holidays should not be compared raw with a September that has none. Per-month grouping of the tickets themselves is covered in Group Excel Rows by Month and Quarter with pandas.

Common pitfalls

SymptomCauseFix
Every count one lower than Excelbusday_count excludes the end dateAdd one day to the end
ValueError: Could not convert object to NumPy datetimeNaT or text dates in the columnMask missing values; parse with pd.to_datetime
ValueError: Non-business day date in busday_offsetStart on a weekend with default roll="raise"Pass roll="backward" (positive) or "forward" (negative)
Holidays have no effectHoliday dates are strings or timestamps with timesConvert the list to datetime64[D]
Negative countsEnd date before start dateValidate order, or take the absolute value if intended
Dates shifted by timezoneTimezone-aware timestampsConvert to local time and drop the zone first

Performance and scale notes

busday_count and busday_offset are vectorised in C, so a million rows take a fraction of a second — far faster than apply with a Python function, and faster than generating bdate_range per row. Building a np.busdaycalendar(weekmask=..., holidays=...) once and passing it as busdaycal= avoids re-validating the holiday list on every call when the same calendar is used repeatedly.

Conclusion

np.busday_count and np.busday_offset are the vectorised equivalents of NETWORKDAYS and WORKDAY. Convert columns to datetime64[D], mask missing dates, and account for the conventions: add a day to the end to count both ends like NETWORKDAYS, and roll backward for positive offsets to match WORKDAY on weekend start dates. Load holidays from the workbook, use a weekmask for non-standard weekends, and keep missing results as missing so they never count as breaches.

Frequently asked questions

What is the pandas equivalent of NETWORKDAYS? numpy.busday_count on the start and end columns converted to datetime64D, with one day added to the end so both dates are counted as Excel does.

How do I exclude public holidays? Pass an array of holiday dates as holidays to busday_count or busday_offset. Reading them from a sheet in the same workbook lets non-developers maintain the list.

Can pandas CustomBusinessDay do the same thing? Yes, for adding working days: a CustomBusinessDay offset with holidays shifts dates correctly. For counting days between two columns, busday_count is simpler and much faster.

How do I count working hours instead of days? Count full working days between the dates, then add the partial hours of the first and last day from the timestamps. Business-hour offsets in pandas can help, but explicit arithmetic is easier to verify.

More on dates & times

More on dates & times →