If you are managing a project timeline, calculating payroll, or tracking how long a support ticket took to resolve, simple subtraction isn’t enough. If you just subtract the start date from the end date, Excel will give you the total number of calendar days—including weekends.
For business applications, you need to calculate working days. The NETWORKDAYS function does exactly this. It automatically calculates the difference between two dates while stripping out all Saturdays and Sundays.
The Syntax of NETWORKDAYS
The function is very simple to use and only requires two pieces of information, with an optional third.
Syntax: =NETWORKDAYS(start_date, end_date, [holidays])
- start_date: The day the project began (usually a cell reference).
- end_date: The day the project finished.
- holidays: (Optional) A range of cells containing specific dates that should also be excluded from the count, such as Christmas or Thanksgiving.
Basic Example: Excluding Weekends
Imagine you have a project start date in cell A2 (e.g., October 1) and an end date in cell B2 (e.g., October 31). You want to know how many actual working days the team spent on the project.
- Click on an empty cell where you want the answer to appear (e.g., cell C2).
- Type the formula:
=NETWORKDAYS(A2, B2) - Press Enter.
Excel will analyze the calendar between October 1 and October 31, completely ignore the 8 weekend days, and return the true number of working days (e.g., 23).
Advanced Example: Excluding Weekends AND Holidays
Weekends are only part of the equation. If your team had two days off for a national holiday during that project, your calculation will be wrong unless you tell Excel about it.
- First, create a separate list of holiday dates somewhere in your spreadsheet (for example, in cells E2 through E10). Type the exact dates of the holidays in these cells.
- Click on the cell where you want your calculation.
- Type the formula, including the new holiday range:
=NETWORKDAYS(A2, B2, E2:E10) - Press Enter.
Excel will now calculate the days between the start and end date, subtract all Saturdays and Sundays, and also subtract any of the dates listed in column E if they happened to fall on a weekday during that period.
What if Your Weekend isn’t Saturday/Sunday?
The standard NETWORKDAYS function hard-codes the weekend as Saturday and Sunday. If you work in an industry or a country where the weekend is Friday/Saturday, or if you only have one day off a week (e.g., Sunday), you must use a slightly different function: NETWORKDAYS.INTL.
=NETWORKDAYS.INTL(A2, B2, 7)
In this function, the third argument is a special code that defines the weekend. (The number ‘7’ tells Excel that the weekend is Friday and Saturday). When you type the formula in Excel, a handy drop-down menu will appear showing you all the different weekend codes you can choose from.