Count working days between two dates in Excel with NETWORKDAYS
Subtracting one date from another gives calendar days, weekends included. NETWORKDAYS counts only the working days between two dates, and takes an optional list of holidays to exclude as well. This tutorial runs it on a November project and checks every number it returns.
| Checked | |
|---|---|
| Apps | Excel, Google Sheets, LibreOffice |
| Versions | LibreOffice 24.2.7.2 (recalculation) · Python 3.12.3 · openpyxl 3.1.2 |
| Read time | 6 minutes |
Measured result — from the recalculated file
| Project: Monday 2 November to Monday 30 November 2026 | |
| Calendar days (end − start) | 28 |
| Working days | 21 |
| Working days excluding holidays | 19 |
| Weekend days in the span | 8 |
| Working days left as of 16 November | 10 |
| Short example, 2–6 November | 5 |
Download the spreadsheet: working-days-between-dates.xlsx (5,313 bytes)
The file that was recalculated and checked. It contains the start and end dates, the holiday list, and the NETWORKDAYS formulas.
1What you need
- Excel, Google Sheets, or LibreOffice. NETWORKDAYS works the same in all three for this tutorial.
- The file above, or a blank workbook if you want to type the formulas in yourself.
2The dates
The project starts Monday 2 November 2026 and ends Monday 30 November 2026. Both dates are stored as real dates in the file, not as text. Microsoft's guidance is to enter dates with the DATE function or as the result of another formula, because dates entered as text can cause problems (NETWORKDAYS function — Microsoft Support).
3Calendar days first
Subtracting the start date from the end date gives the calendar difference:
B5 =B4-B3Result: 28. That counts the days between the two dates, weekends included, and does not count the start date itself. It is the number to compare against — the working-day count below must be smaller, by exactly the number of weekend days.
4NETWORKDAYS counts the working days
NETWORKDAYS returns the number of whole working days between the start and end dates, excluding weekends and any dates in the holidays list:
B6 =NETWORKDAYS(B3, B4)Result: 21. The function counts both endpoints: 2 November is a Monday and 30 November is a Monday, and both are counted. The short example in the file proves it — Monday 2 to Friday 6 November returns 5, not 4:
B11 =NETWORKDAYS(DATE(2026,11,2), DATE(2026,11,6))5Excluding the holidays
Two dates are listed as holidays: Remembrance Day, Wednesday 11 November, and a company shutdown day, Friday 27 November. Passing the list as the third argument excludes them:
B7 =NETWORKDAYS(B3, B4, $E$3:$E$4)Result: 19 — the 21 working days minus the two holidays, both of which fall on weekdays. Keep the holiday list in its own cells, as the file does, so next year's dates can be swapped in without touching the formula.
6The weekend check
The span from 2 to 30 November inclusive is 29 days. Working days plus weekend days must add to 29:
B8 =B5+1-B6Result: 8 weekend days — four Saturdays and four Sundays. 21 + 8 = 29, so the counts reconcile. If they did not, one of the dates would be wrong.
7Working days left
The same function answers “how much working time is left” from any date inside the project. As of Monday 16 November:
B9 =NETWORKDAYS(DATE(2026,11,16), B4, $E$3:$E$4)Result: 10 — five days in the week of 16 November, four in the week of 23 November (the 27th is a holiday), and Monday 30 November itself.
8A different weekend
NETWORKDAYS always treats Saturday and Sunday as the weekend. If a project runs on a different weekly pattern, Microsoft points to NETWORKDAYS.INTL, which takes extra arguments for which days count as weekend days. That function is not in the file — the project here runs Monday to Friday — but the pattern is the same: start date, end date, weekend definition, then the holiday list.
Traps
Both endpoints count. Monday to Friday is 5 working days, not 4. Subtracting dates gives the gap between them; NETWORKDAYS counts the days themselves, start and end included.
Dates stored as text return #VALUE! If either date argument is not a valid date, NETWORKDAYS returns the #VALUE! error. Type dates with the DATE function, or enter them in cells formatted as dates, and do not paste them in as text.
Forgetting the holiday list. Without the third argument the same project returns 21, not 19. The two holidays are easy to miss because the formula still returns a plausible number.
How this was checked
The workbook was built with openpyxl 3.1.2 (formulas only, no typed-in results), recalculated by LibreOffice 24.2.7.2 in headless mode, and read back with openpyxl in data-only mode. The expected values were computed independently in Python 3.12.3 by counting weekdays between the dates, minus the holidays: 28 calendar days, 21 working days, 19 excluding holidays, 8 weekend days, 10 working days left as of 16 November, and 5 for the short example. All six matched exactly.
Sources
- NETWORKDAYS function — Microsoft Support — the definition, the syntax with the optional holidays argument, the DATE-function guidance, and the pointer to NETWORKDAYS.INTL for a different weekend.