Description
Use this function to return the number of working days between two dates. You can also specify both which days are to be considered weekend (non-working) days and the holidays to be excluded.
Syntax
NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])
Inputs
This function has the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
start_date |
Yes | First date of the range to be considered. | A cell reference, an integer date, or a date in the format DATEVALUE(DD,MM,YYYY) or DATEVALUE(YYYY,MM,DD). Alternatively they can be a quotated string, such as "1/14/2023". A formula which results in one of these is also acceptable. |
end_date |
Yes | Last date of the range to be considered. | A cell reference, an integer date, or a date in the format DATEVALUE(DD,MM,YYYY) or DATEVALUE(YYYY,MM,DD). Alternatively they can be a quotated string, such as "1/14/2023". A formula which results in one of these is also acceptable. |
weekend |
No | Which days of the week should be considered weekends. (repetitive non-working days) | See Configuring weekends below. |
holidays |
No | A list of non-work days as supplied as dates. | A cell reference, a cell range (D2:D13), an integer date, or a date in the format DATEVALUE(DD,MM,YYYY) or DATEVALUE(YYYY,MM,DD). Alternatively they can be a quotated string, such as "1/14/2023". A formula which results in one of these is also acceptable. |
Example
Configuring weekends
This function provides two options to configure weekends. The first option is to supply a number as shown in the table below (the values 8, 9, and 10 are not supported).
| Weekend days | Weekend number |
|---|---|
| Saturday, Sunday | 1 (default) |
| Sunday, Monday | 2 |
| Monday, Tuesday | 3 |
| Tuesday, Wednesday | 4 |
| Wednesday, Thursday | 5 |
| Thursday, Friday | 6 |
| Friday, Saturday | 7 |
| Sunday only | 11 |
| Monday only | 12 |
| Tuesday only | 13 |
| Wednesday only | 14 |
| Thursday only | 15 |
| Friday only | 16 |
| Saturday only | 17 |
The second way to configure weekends is to provide a 7 character text string enclosed in quotation marks composed of 1s and 0s, where "1" indicates a non-work day and "0" indicates a work day. The first position represents Monday.
Sample data
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | End Date | Holidays |
| 2 | 1/1/2024 |
12/31/2024 |
5/15/2024 |
| 3 | 1/1/2024 |
12/31/2024 |
6/15/2024 |
| 4 | 1/1/2024 |
12/31/2024 |
7/4/2024 |
| 5 | 7/19/2003 |
February 31, 2002 |
7/5/2024 |
| 6 | 41631 |
23-Jan-14 |
9/6/2024 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Determine the number of workdays (Mon-Fri) between the dates specified in two cells, with no holidays specified. Saturday and Sunday are the weekend days. | =NETWORKDAYS.INTL(A2,B2) |
This formula determines the number of workdays (Mon-Fri) between the date in cell A2 and the one in cell B2, with no holidays specified. Saturday and Sunday are assumed to be the weekend days. Result: 262. |
| Determine the number of workdays (Mon-Fri) between the dates specified in two cells, with the holidays specified in a third cell. Saturday and Sunday are the weekend days. | =NETWORKDAYS.INTL(A2,B2,,C2) |
This formula determines the number of workdays (Mon-Fri) between the date in cell A2 and the cell B2, with the holidays specified in C2. Result: 261. |
| Determine the number of workdays (Mon-Fri) between the dates specified in two cells, with the holidays specified in a cell range. Saturday and Sunday are the weekend days. | =NETWORKDAYS.INTL(A3,B3,,C2:C5) |
This formula determines the number of workdays (Mon-Fri) between the date in cell A3 and the cell B3, with the holidays specified in the range C2:C5. Note that 6/15/2024 (C3) falls on a Saturday, so only the three weekday holidays (5/15, 7/4, 7/5) reduce the count. Result: 259. |
| Determine the number of workdays between a specified date and a date in a cell, with no holidays. The workdays are Monday, Tuesday, and Wednesday. | =NETWORKDAYS.INTL("1/1/2024",B4,"0001111") |
This formula determines the number of workdays between 1/1/2024 and the value in cell B4, using Monday, Tuesday, Wednesday as the workdays, with no holidays specified. Result: 158. |
| Determine the number of workdays between two specified dates, with no holidays. The weekend (non-work) days are Tuesday and Wednesday. | =NETWORKDAYS.INTL(DATE(2024,11,1),DATE(2024,12,31),4) |
This formula determines the number of workdays between 11/1/2024 and 12/31/2024. The weekend (non-work) days are Tuesday and Wednesday (4). No holidays specified. Result: 44. |
| Determine the number of workdays (Mon-Fri) between the dates specified in two cells. Saturday and Sunday are the weekend days. There is one holiday specified. | =NETWORKDAYS.INTL(A6,B6,"0000011",45651) |
This formula determines the number of workdays (Mon-Fri) between the date in cell A6 and the one in cell B6. The non-work days are Saturday and Sunday ( Result: 24. |
Notes
- NETWORKDAYS.INTL
- Doesn't implicitly know which days are holidays. These have to be specified by the user.
- Includes both the start date and end date when calculating workdays.
- Automatically excludes all Saturdays and Sundays in the specified range.
- Ignores any time values.
- If the holiday date(s) aren't between the specified start and end dates, they're ignored.
- Wildcards don't work with this function.
- If
start_dateis later thanend_date, the return value will be negative, and the magnitude will be the number of whole workdays. - If either or both
start_dateorend_dateis out of range for the current date base value, NETWORKDAYS.INTL returns the #NUM! error value. - If a weekend string is of invalid length or contains invalid characters, NETWORKDAYS.INTL returns the #VALUE! error value.
- If the provided date(s) aren't valid, NETWORKDAYS returns the #VALUE! error.
Tips
- You can use this function to calculate the number of working hours in the specified range by multiplying the result by the appropriate number of working hours.