FORMULAS & FUNCTIONS
NETWORKDAYS in Excel: Count Business Days Between Dates (with Holidays)
How many working days are there between two dates? Subtracting the dates gives calendar days, which is the wrong answer for payroll, leave, project schedules and service-level deadlines. NETWORKDAYS skips weekends, and with a holiday list it skips holidays too. This guide builds the list, counts business days for every month of Q4 2026, handles six-day weeks, finds due dates with WORKDAY, and fixes the most common reason a holiday is not excluded.
What you will build
| Area | Cells | What it holds |
|---|---|---|
| Periods | A3:G9 | Start and end dates (inputs), calendar days, weekdays, business days and a six-day-week count |
| Holiday list | I3:J10 | Five holidays in a named range called Hols, with a check that every entry is a real date |
| Due dates | A13:D16 | WORKDAY: a start date plus a number of business days |
| Broken list | I13:J17 | A holiday list copied from last year, to show why a holiday is not excluded |
Step 1: Build the holiday list
Type each holiday as a real date in one column (J4:J8 here) with its name next to it. Then select J4:J8 and type a name in the Name Box, left of the formula bar, or use Formulas > Define Name. The practice file calls it Hols. A name is easier to read than $J$4:$J$8 and never slides when you copy a formula.
=IF(COUNT(J4:J8)=ROWS(J4:J8),"All dates","Text found")COUNT only counts numbers, and Excel dates are numbers, so this check in J10 warns you if any holiday was typed or imported as text. If it says Text found, standardize the dates first.
Step 2: Count weekdays with NETWORKDAYS
The syntax is NETWORKDAYS(start_date, end_date, [holidays]). Without the holidays argument it counts Monday to Friday. In E4:
=NETWORKDAYS(B4,C4)Both the start and the end date are counted if they are weekdays, so a single Monday returns 1, not 0. That is different from simple subtraction: the calendar-day column uses =C4-B4+1 to include both ends as well.
Step 3: Exclude holidays
Add the holiday list as the third argument. In F4, filled down:
=NETWORKDAYS(B4,C4,Hols)Excel removes every listed holiday that falls on a weekday inside the period. A holiday on a Saturday or Sunday is not subtracted twice, and holidays outside the period are simply ignored.
That last row is exactly how a leave tracker should count days taken: an employee who books Thanksgiving week off uses 3 days of leave, not 5.
Step 4: Different weekends with NETWORKDAYS.INTL
Not every business works Monday to Friday. NETWORKDAYS.INTL adds a weekend argument. In G4:
=NETWORKDAYS.INTL(B4,C4,11,Hols)| Weekend code | Days off |
|---|---|
| 1 (default) | Saturday and Sunday |
| 2 | Sunday and Monday |
| 7 | Friday and Saturday |
| 11 | Sunday only |
| 17 | Saturday only |
"0000011" | Text version: seven digits, Monday to Sunday, 1 = day off |
"0010011" for a Wednesday half-day plus the weekend.Step 5: Find a due date with WORKDAY
NETWORKDAYS counts the days between two dates. WORKDAY goes the other way: it returns the date a given number of business days after a start date. In D14, filled down:
=WORKDAY(B14,C14,Hols)WORKDAY does not count the start date itself, so 1 business day after a Friday is the following Monday. Use a negative number to go backwards, and WORKDAY.INTL for other weekends. Format the result as a date if Excel shows a serial number.
=InvoiceDate+30, and the AR aging report ages in calendar days. Use WORKDAY only where a contract, policy or service level actually says business days.Step 6: NETWORKDAYS not excluding holidays? Check these
The second holiday list in I13:J15 was copied from last year's file. Thanksgiving still says Nov 27, 2025, so for November 2026 it excludes nothing:
=NETWORKDAYS(B5,C5,J14:J15)=SUMPRODUCT((J14:J15>=B5)*(J14:J15<=C5))
The first formula returns 20 instead of the 19 you would expect with Veterans Day and Thanksgiving, and the second shows that only 1 of the listed holidays falls inside November. No error appears, which is why this mistake survives. Other causes:
| Symptom | Cause | Fix |
|---|---|---|
| A holiday is not excluded | Wrong year, or the holiday is stored as text | Check the year; use the COUNT check from Step 1. |
| Copied formulas give different answers | The holiday range is relative and slid down | Use a name or lock it with $ (press F4). |
#VALUE! | The start or end date is text Excel cannot read as a date | Convert it to a real date (DATEVALUE or Text to Columns). |
| A negative number | Start and end dates are reversed | Swap them, or wrap in ABS if direction does not matter. |
| A number like 46357 instead of Dec 1, 2026 | WORKDAY result formatted as General | Format the cell as a date (Ctrl+1). |
Where to use it
Business-day counts turn up everywhere: working days in a payroll month, days of leave taken, and days remaining on a project tracker. Combine NETWORKDAYS with TODAY() to count business days left until a deadline, =NETWORKDAYS(TODAY(),C4,Hols), and with COUNTIFS to count overdue items. For hours rather than days, see the hours worked formula, and to add up amounts within a date range, SUMIFS between two dates.
Frequently asked questions
Does NETWORKDAYS include the start and end date?
Yes. Both are counted if they are working days. NETWORKDAYS for Monday to Friday of the same week returns 5.
How do I add holidays to NETWORKDAYS?
List the holidays as real dates in a range and pass that range as the third argument: =NETWORKDAYS(B4,C4,$J$4:$J$8), or use a named range.
How do I count business days with Saturday as a working day?
Use NETWORKDAYS.INTL with weekend code 11 (Sunday only): =NETWORKDAYS.INTL(B4,C4,11,Hols).
What is the difference between NETWORKDAYS and WORKDAY?
NETWORKDAYS counts working days between two dates. WORKDAY adds a number of working days to a date and returns the resulting date.
Why does NETWORKDAYS return a negative number?
The start date is later than the end date. Swap the two dates.
Final checklist
- Holidays are real dates for the right year, in one named or locked range.
- The COUNT check shows every holiday is a date.
- Start and end dates are in the right order.
- NETWORKDAYS.INTL is used for any weekend other than Saturday-Sunday.
- WORKDAY results are formatted as dates.
- Calendar-day terms (like net 30) use plain date arithmetic, not WORKDAY.
Plan around real working days
Browse the OneXcel Tutorial Hub for more date formulas, explore ready-to-use Excel templates including leave and project trackers, or turn your data into a dashboard with the OneXcel Dashboard Builder.