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 | 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 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. This can be 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) |
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 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 (start_date) |
B (end_date) |
C (holidays) |
NETWORKDAYS result |
E (Holidays) |
|---|---|---|---|---|
| 1/1/2024 | 12/31/2024 | 262 | 5/15/2024 | |
| 1/1/2024 | 12/31/2024 | 5/15/2024 | 261 | 6/15/2024 |
| 1/1/2024 | 12/31/2024 | 258 | 7/4/2024 | |
| 7/19/2003 | February 31, 2002 | #VALUE! | 7/5/2024 | |
| 41631 | 23-Jan-14 | 24 | 9/6/2024 |
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 functions
| 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. For this data set, the formula returns 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. For this data set, the formula returns 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,,E2:E5) |
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. For this data set, the formula returns 64. |
| 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. For this data set, the formula returns 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 1/11/2024 and 31/12/2024. The weekend (non-work) days are Tuesday and Wednesday (4). No holidays specified. For this data set, the formula returns 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(A5,B5,"1111100",45651) |
This formula determines the number of workdays (Mon-Fri) between the date in cell A2 and the one in cell B2. The non-work days are Saturday and Sunday For this data set, the formula returns 78. |
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.
Related functions
DATE
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
WEEKDAY
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC