Excel WORKDAY and NETWORKDAYS: skip weekends and holidays

Somebody in accounting asks when the invoice is due — thirty business days after the client signed. You type =A2+30, hand back the date, and it lands on a Saturday that’s also the day after a public holiday. The client’s ops team ignores it. The invoice ages an extra week. Excel gave you a calendar-day answer to a working-day question, and calendar math and work-week math are not the same thing. WORKDAY and NETWORKDAYS are the two functions built for the difference.

Why simple date math misses the point

Excel stores every date as an integer — 2026-08-03 is just 46237 under the hood. That makes +30 and end - start tempting, and for a birthday countdown they work fine. For anything ruled by a work calendar they do not, because addition and subtraction have no concept of Saturday, Sunday, or a public holiday. Every result includes days nobody works.

The two functions solve two sides of the same question. WORKDAY takes a start date and a count, then returns the date that lands that many working days later. NETWORKDAYS takes a start and an end date and returns the count of working days in between. Both accept an optional list of holidays and both, by default, treat Saturday and Sunday as the weekend. That default matters because it’s what breaks first in every non-US or non-office context — we come back to that below.

=A2+30

=WORKDAY(A2,30,Holidays)

WORKDAY: land on the next real working day

WORKDAY answers “what’s the date N business days from here?” — the shape a due-date column, a project deadline, or an SLA calculator needs. Its signature is short:

=WORKDAY(start_date, days, [holidays])

start_date is the anchor, days is the count to move forward, and [holidays] is an optional range or array of dates to skip. Positive days move forward; negative days move backward. That last part is easy to miss and turns out to be useful: =WORKDAY(TODAY(),-5,Holidays) is “five working days ago”, which is what any late-payment reminder should compare against.

Here is a due-date column driven off a signed-date column with a named range Holidays on another sheet:

C2 resolves to 2026-08-12, not 2026-07-30. The gap is the two weekends and the July 4 observance between the signed date and the thirtieth working day. Format the result column as a short date if Excel shows the underlying serial number — WORKDAY returns a serial, and cell formatting decides how it renders.

NETWORKDAYS: count the real days between two dates

NETWORKDAYS answers the mirror question — “how many working days are in this range?” — which is what timesheets, aging reports, and turnaround-time dashboards actually want. Same three-argument shape:

=NETWORKDAYS(start_date, end_date, [holidays])

Both endpoints are counted. Give it the same date for start and end on a Wednesday and the result is 1, not 0 — a subtle thing that trips people who expect exclusive ranges. According to the Microsoft NETWORKDAYS reference, the function returns #VALUE! if any argument fails to parse as a date, which is the single most common failure and the one covered in the troubleshooting section below.

A common pattern is turnaround time per ticket. Column A holds the open date, column B the resolved date, column C uses NETWORKDAYS with a named Holidays range so a closed ticket on a Monday after a Friday holiday reads as one working day rather than four:

=NETWORKDAYS(A2, B2, Holidays)

Pair it with a simple average — =AVERAGE(C2:C500) — and you have “average working days to resolve” without a helper column filtering weekends out by hand.

Feed holidays as a Table, not a static range

The [holidays] argument accepts any range that returns dates. A fixed range like Sheet2!$A$2:$A$20 works — until the twentieth date fills up and the twenty-first holiday you add sits outside the range and silently gets counted as a working day. The fix is a one-time setup: put the holiday list in an Excel Table and reference the Table column.

1. Put one holiday date per row on a Holidays sheet, with a header cell like “Date”.
2. Select the range and press Ctrl+T. Confirm “My table has headers”.
3. On the Table Design tab, rename the table to tblHolidays.
4. Reference the column in formulas as tblHolidays[Date].

Now =WORKDAY(B2,30,tblHolidays[Date]) auto-expands the moment someone appends a new observance to the bottom of the table. No stale range, no forgotten update. This is the single change that turns WORKDAY and NETWORKDAYS from “one-off calculation” into infrastructure a team can actually rely on. If you build a lot of formulas off Tables, our guide to the essential Excel formulas every analyst leans on covers structured references in more depth.

When Monday–Friday isn’t your work week

The default weekend is Saturday and Sunday, and that assumption is baked into WORKDAY and NETWORKDAYS with no way to change it. Retail runs Sunday to Saturday with Wednesday off. Middle-East offices work Sunday to Thursday. A four-day week has Monday to Thursday. For any of these, the base functions lie. The .INTL variants exist for exactly this reason, with a fourth argument — the weekend code — that reshapes what “not working” means.

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
=WORKDAY.INTL(start_date, days, [weekend], [holidays])

The weekend argument takes either a number code or a seven-character binary string. The string form is easier to read once you learn it: seven characters, Monday first, 1 means non-working, 0 means working. So "0000011" is the default (Sat and Sun off), "0000110" is Fri and Sat off, and "0001000" is a Thursday-only weekend.

The full number-code list is documented in the NETWORKDAYS.INTL reference. Pick whichever form you’ll re-read six months from now — most people find the string easier because it’s self-documenting. A retail schedule with Wednesday off looks like "0010000", and anyone reading the formula can count over from Monday to see what it means.

Fix the errors before they land

Three failure modes account for almost every reported problem with these functions. Ranked by how often they bite:

The fix is to wrap the offending cell in DATEVALUE() — =NETWORKDAYS(DATEVALUE(A2), DATEVALUE(B2), tblHolidays[Date]) — or, better, convert the column once using Text to Columns with the Date format. One-time conversion beats formula-level patching, because the ISNUMBER check keeps working elsewhere in the workbook.

#NUM! shows up in two situations. The first is a reversed range in NETWORKDAYS — start date later than end date — which the classic function accepts and returns a negative count, but the .INTL variant rejects outright when the weekend code is also invalid. The second is a malformed weekend string: exactly seven characters, only 0 and 1, and not all sevens. "1111111" — every day is a weekend — throws #NUM! rather than returning zero, because Excel would loop forever hunting for a working day.

The third failure is quieter and worse: the formula returns a plausible number but the holiday list is wrong. Symptoms include a Christmas Day counted as a working day (holiday range doesn’t reach that far) or a duplicate holiday causing a phantom skip. This is why the Table pattern above matters — it removes the class of bug where “the range got stale” is the answer.

Pick one and start with it

If you build due-date columns, start with WORKDAY and a proper holiday Table. If you build aging or turnaround reports, start with NETWORKDAYS on the same Table. The .INTL variants come out of the box the first time someone asks about a non-standard week, and by then the muscle memory is already there. For a template that already assumes working-day math, the project timeline tracker is a reasonable place to see the pattern applied end to end.

• ✓ Holidays live in an Excel Table, referenced as tblHolidays[Date]
• ✓ Date columns pass =ISNUMBER(), not stored as text
• ✓ Non-Mon–Fri weeks use the .INTL variant with an explicit weekend code
• ✓ Result cells are formatted as short date, not general number