NETWORKDAYS Generator
Count the working days between two dates, skipping weekends and a list of holidays.
NETWORKDAYS
Count the working days between two dates, skipping weekends and a list of holidays.
Cell holding the first date. This day is counted when it falls on a weekday and is not in the holidays list.
Cell holding the last date. This day is counted too when it falls on a weekday and is not in the holidays list.
Range of dates to skip. Lock it with $ signs so it does not shift when you copy the formula down. This argument is optional - with no holiday list, delete it along with the comma before it.
=NETWORKDAYS(A2, B2, $E$2:$E$12)Worked Example
A2 holds 1/1/2026, B2 holds 1/31/2026, and the holidays range $E$2:$E$12 lists New Year's Day (1/1/2026).
=NETWORKDAYS(A2, B2, $E$2:$E$12)Returns: 21 - January 2026 has 22 weekdays, and New Year's Day (a Thursday) is deducted as a holiday.
Checks Before You Paste
- •NETWORKDAYS counts both endpoints, so Monday to Friday returns 5, not 4. Subtract 1 if you actually want elapsed days rather than days worked.
- •Weekends are hard-wired to Saturday and Sunday. For any other pattern use NETWORKDAYS.INTL(start, end, weekend, holidays) - weekend takes a code (1 = Sat/Sun, 7 = Fri/Sat, 11 = Sunday only) or a 7-character string starting at Monday, such as "0000011". It needs Excel 2010 or later, and works in Google Sheets.
- •If the answer shows up as a date like 1/21/1900, the cell inherited date formatting from its arguments - set it back to General or Number. Keep the holidays range as real date values rather than text that only looks like a date: format that column as a date and check the entries right-align. A start or end date Excel cannot read as a date returns #VALUE!.
How NETWORKDAYS works
Availability: NETWORKDAYS is built in from Excel 2007 onward; in Excel 2003 it lived in the Analysis ToolPak add-in and had to be switched on. NETWORKDAYS.INTL, which lets you define the weekend, arrived in Excel 2010. Both are in Excel for Mac, Excel for the web and Google Sheets, and Sheets uses the same weekend codes and string masks.
NETWORKDAYS answers a specific question: how many working days lie between two dates, counting both of them, ignoring Saturdays and Sundays, and skipping any dates you list as holidays. It is the function behind service-level calculations, project durations and payroll day counts, and its result is a plain number rather than a date.
Both endpoints are included. Monday to Friday of the same week returns 5, not 4, which is right for "how many days will this take" and wrong for "how many days until". Subtract 1 when you want elapsed working days rather than working days occupied. This inclusivity is the single most common source of an off-by-one in a schedule.
The weekend is hard-wired to Saturday and Sunday. Any other pattern - a Friday/Saturday weekend, a six-day working week, Sundays only - needs NETWORKDAYS.INTL, whose third argument takes either a numeric code (1 for Sat/Sun, 7 for Fri/Sat, 11 for Sunday only, and so on through 17) or a seven-character string of ones and zeros starting at Monday, where 1 marks a non-working day. "0000011" is the standard weekend written the long way; "0000000" makes every day a working day.
The holidays argument is an ordinary range of date cells. Excel does not know about public holidays, so this list is yours to maintain, and it must contain real dates rather than text that looks like a date. Any argument Excel cannot read as a date - in either endpoint or in the holidays range - returns #VALUE! rather than being skipped, so a single text entry in a holiday column breaks every formula that points at it. Duplicated holidays are counted once, and holidays falling on a weekend or outside the date range are ignored.
Syntax
=NETWORKDAYS(start_date, end_date, [holidays])| Argument | Required | What it does |
|---|---|---|
start_date | Required | The first date of the period, as a cell reference, a DATE() call, or a serial number. It is included in the count when it falls on a weekday and is not in the holidays list. Any time-of-day component is ignored, so 09:00 on a Monday still counts as a whole day. |
end_date | Required | The last date of the period, included on the same terms. It may be earlier than start_date, in which case the function returns a negative number rather than an error. |
holidays | Optional | A range or array of dates to exclude - typically a locked column such as $E$2:$E$12. Omit it entirely, along with the comma before it, when there is no holiday list. Entries must be genuine dates; text dates return #VALUE!. Duplicates, weekend dates and dates outside the period have no effect. |
More worked examples
A2 holds 2 March 2026 (a Monday) and B2 holds 6 March 2026 (the Friday of the same week), with no holiday list.
=NETWORKDAYS(A2, B2)Returns: 5
Both endpoints count, which is why a Monday-to-Friday span is 5 rather than 4. For the number of nights or elapsed working days, subtract 1.
A support ticket opened 22 December 2026 and closed 5 January 2027, with $E$2:$E$4 listing 25 December 2026, 28 December 2026 and 1 January 2027 as company holidays.
=NETWORKDAYS(A2, B2, $E$2:$E$4)Returns: 8
There are 11 weekdays in that span; the three listed holidays all fall on weekdays (Friday, Monday and Friday), so all three are deducted. Had one landed on a Saturday it would have made no difference to the total.
A rota where staff work Monday to Saturday, counting shifts across the whole of March 2026 - A2 holds 1 March and B2 holds 31 March.
=NETWORKDAYS.INTL(A2, B2, 11)Returns: 26
Weekend code 11 means Sunday only. March 2026 contains five Sundays, so 31 days less 5 gives 26. The equivalent string form is "0000001", with the 1 in the seventh position for Sunday.
Common mistakes
- The answer appears as a date such as 1/21/1900
- The result cell inherited date formatting from the cells the formula points at. The number is correct - 21 working days is being drawn as the 21st day of 1900. Select the cell and set the number format back to General or Number.
- Dates that are really text return #VALUE!
- Dates pasted from a web page or a CSV often arrive as text, and NETWORKDAYS refuses them. The quick check is alignment: real dates right-align in a General cell, text dates left-align. Fix the column with Data > Text to Columns > Finish, or wrap the arguments in DATEVALUE. A text entry anywhere in the holidays range breaks the formula just as thoroughly as one in an endpoint.
- Off by one because both endpoints count
- A task starting and finishing on the same working day returns 1, not 0, and a Monday-to-Friday project returns 5. If your schedule model expects elapsed days, subtract 1: =NETWORKDAYS(A2, B2)-1. Deciding this once and applying it consistently matters more than which convention you pick.
- A negative result when the dates are the wrong way round
- =NETWORKDAYS(B2, A2) with the later date first returns something like -21. This is documented behaviour, not an error, and it silently poisons any SUM built on the column. Guard it with =IF(B2<A2, "Check dates", NETWORKDAYS(A2, B2)) when the data entry order is not reliable.
- The holidays range shifting when the formula is filled down
- =NETWORKDAYS(A2, B2, E2:E12) copied down row by row becomes E3:E13, then E4:E14, quietly dropping holidays off the top of the list. The count creeps upward and nothing errors. Lock it as $E$2:$E$12, or convert the holiday list to a named range or table column.
Frequently Asked Questions
Yes, both, provided each falls on a weekday and is not in the holidays list. Monday to Friday returns 5. If you need elapsed working days between the two - the number of working days that pass, rather than the number occupied - subtract 1 from the result.
Use NETWORKDAYS.INTL with weekend code 7: =NETWORKDAYS.INTL(A2, B2, 7, $E$2:$E$12). The codes run 1 to 7 for two-day weekends and 11 to 17 for single-day weekends. For anything unusual, pass a seven-character string starting at Monday instead - "1000001" means Monday and Sunday are the non-working days.
They answer opposite questions. NETWORKDAYS takes two dates and returns a count of working days. WORKDAY takes a start date and a number of working days and returns the date you land on: =WORKDAY(A2, 10, $E$2:$E$12) gives the due date ten working days out. WORKDAY.INTL adds the same custom weekend argument.
Because the cell picked up date formatting, usually from being adjacent to or copied from the date columns the formula references. The underlying value is a correct count; only the display is wrong. Home > Number Format > General fixes it, and it will not come back once the cell is explicitly formatted.
No. A holiday on a Saturday or Sunday is already excluded as a weekend and listing it changes nothing. Listing it does no harm either, so a standard national-holiday range can be reused across workbooks without pruning. What does matter is including the substitute weekday your organisation actually takes off, since Excel has no idea that exists.
Related Tools
Date Difference Generator
Count calendar days, months or years between two dates when weekends should not be excluded.
Excel Date Converter
Turn serial numbers and text dates into real date values the function will accept.
Gantt Chart in Excel
Turn start dates and working-day durations into a project timeline.