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 | Description | 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. Result: 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. Result: 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. Result: 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:
Result: $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:
Result: "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.