Description
Use this function to return the number of working days between two dates. It automatically excludes Saturdays and Sundays and optionally exclude a set of holidays that you supply.
Syntax
NETWORKDAYS(start_date,end_date,[holidays])
Inputs
This function has the following arguments:
| Name | Required | Valid input |
|---|---|---|
start_date |
Yes | First date of the range to be considered. This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable. Quotation marks are required. |
end_date |
Yes | Last date of the range to be considered. This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable. Quotation marks are required. |
holidays |
No | A list of non-work days as supplied as dates. This can be a cell reference, a cell range (D2:D13), an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable. |
Example
Sample data
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start date | End date | Holidays | Rate | Target |
| 2 | 1/1/2024 | 12/31/2024 | 5/15/2024 | $262.00 | 122 |
| 3 | 1/1/2024 | 3/31/2024 | 3/15/2024 | $261.00 | 14 |
| 4 | 11/1/2024 | 12/31/2024 | 11/4/2024 | $258.00 | 12 |
| 5 | 2/5/2025 | 11/7/2025 | 7/4/2025 | $300.00 | 21 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Determine the number of workdays (Mon-Fri) between dates provided in cells, not referencing any holidays. | =NETWORKDAYS(A1,B1) |
This formula calculates the number of workdays (Mon-Fri) between the date in cell A1 and the cell B1, with no holidays specified. For this data set this formula returns 262. |
| Determine the number of workdays (Mon-Fri) between dates provided in cells, excluding referenced holiday. | =NETWORKDAYS(A2,B2,"5/15/2024") |
This formula calculates the number of workdays (Mon-Fri) between the date in cell A1 and the cell B1, with the holidays specified in C2. For this data set this formula returns 261. |
| Determine the number of workdays (Mon-Fri) between dates provided in cells, with the holidays specified in a cell range. | =NETWORKDAYS(A1,B1,E1:E5) |
This formula calculates the number of workdays (Mon-Fri) between the date in cell A1 and the cell B1, with the holidays specified in the range C2:C4. For this data set this formula returns 258. |
| Calculate the total cost of a project based on the number of working days and the daily rate. | =PRODUCT(NETWORKDAYS(A2, B2), C2) |
This formula works in the following manner:
For this data set this formula returns $68,382.00 |
| Determine the status of a project and return a string with that status. | =IFS(NETWORKDAYS(A2, B2, E2:E5) <= C2, "On Time", NETWORKDAYS(A2, B2, E2:E5) <= C2*1.1, "Slightly Delayed", TRUE, "Significantly Delayed" ) |
This formula works in the following manner:
For this data set this formula returns "On Time" |
Notes
- NETWORKDAYS doesn't implicitly know which days are holidays. These dates have to be specified by the user.
- NETWORKDAYS includes both the start date and end date when calculating workdays.
- NETWORKDAYS automatically excludes all Saturdays and Sundays in the specified range.
- NETWORKDAYS ignores any time values.
- If the holiday date(s) aren't between the specified start and end dates, they're ignored.
- If the provided date(s) aren't valid, NETWORKDAYS returns the #VALUE! error.
- Wildcards don't work with this function.
Tips
- You can use this to calculate the number of working hours in the specified range by multiplying the result by the appropriate number of working hours.
Related functions
DATE
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
WEEKDAY
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC