Description
Use this function to determine a date that is a given number of working days in the future or past from a specified start date.
You can also specify both which days are to be considered weekend (non-working) days and the holidays to be excluded. Unlike WORKDAY, WORKDAY.INTL can be configured for a custom workweek, where any day of the week can be a workday or non-workday.
This function is useful for calculating start dates, delivery dates, and completion dates that need to factor in both working and non-working days.
Syntax
WORKDAY.INTL(start_date,days,[weekend],[holidays])
Inputs
This function has the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
start_date |
Yes | Date to be used as the base for calculation. | 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. |
days |
Yes | Working days before or after start_date. |
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. |
weekend |
No | Which days of the week are to be treated as working and non-working days. | If weekend is omitted, WORKDAY.INTL will treat Saturdays and Sundays as 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 DD/MM/YYYY or YYYY/MM/DD. If holidays are not provided, WORKDAY.INTL will treat only Saturdays and Sundays as non-working days. |
Example
Configuring weekends
The WORKDAY.INTL function provides two options to configure weekends. The first option is to supply a number as shown in the table below (8, 9, and 10 aren't 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. Example: "1100000" is a week where Monday and Tuesday are non-work days.
Sample data
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Start Date | Offset Days | Holidays | WORKDAY result | Expedited shipping | |
| 2 | 1/1/2024 | 14 | 1/19/2024 | Yes | ||
| 3 | 1/1/2024 | 14 | 1/11/2024 | 1/22/2024 | No | |
| 4 | 1/1/2024 | -14 | 12/12/2023 | No | ||
| 5 | 7/19/2023 | 9 | 12/31/2023 | 8/01/2023 | Yes | |
| 6 | 45283 | 12 | 1/8/2024 | 01/08/2014 | Yes |
Sample formulas
| Use Case | Formula | Explanation and Result |
|---|---|---|
| Determine the date that is the specified number of workdays (Mon-Fri) after the specified date, with no holidays specified. | =WORKDAY.INTL("1-Jul-2024",5) |
This formula works in the following manner:
For this formula, the value returned is 45481 (8 July 2024). |
| Determine the date that is the specified number of workdays (Mon-Fri) after the date specified in a cell, with no holidays specified. | =WORKDAY.INTL(A2,B2) |
This formula works in the following manner:
For this formula, the value returned is 45300 (1/9/2024). |
| Determine the date that is the specified number of workdays (Mon-Fri) between the two dates specified in two cells, with no holidays specified. | =WORKDAY.INTL(A2,-14) |
This formula works in the following manner:
For this formula, the value returned is 45272 (12/12/2023). |
| Determine the date that is the specified number of workdays (Mon-Fri) between the two dates specified in two cells, using the holidays specified in a third cell. | =WORKDAY.INTL(A3,B3,C3) |
This formula works in the following manner:
For this formula, the value returned is 45308 (1/22/2024). |
| Determine the date that is the specified number of workdays (Mon-Fri) between the two dates specified in two cells, using the holidays specified in an additional cell range. | =WORKDAY.INTL(A2,B2,C2:C6) |
This formula works in the following manner:
For this formula, the value returned is 45324 (2/2/2024). |
| Calculate a project's start date by subtracting a specific number of workdays from a deadline. | =WORKDAY.INTL(DATE(2024,3,15),-10) |
This formula works in the following manner:
For this formula, the value returned is 45352 (3/1/2024). |
| Determine a revised project deadline by adding workdays to the original start date, considering the actual work duration (in network days). | =WORKDAY.INTL(DATE(2024,3,1),NETWORKDAYS.INTL(DATE(2024,3,1), DATE(2024,3,15))) |
This formula works in the following manner:
Result: 45369 (3/18/2024). |
| Determine a delivery date adjustment based on whether a condition is met (e.g., expedited shipping being selected). | =IF(A2="Yes", WORKDAY.INTL(DATE(2024,3,8),2), WORKDAY.INTL(DATE(2024,3,8),5)) |
This formula works in the following manner:
Result: 45363 (3/12/2024). |
Notes
- WORKDAY.INTL doesn't implicitly know which days are holidays. These have to be specified by the user.
- WORKDAY.INTL doesn't include the start date as a workday.
- WORKDAY.INTL automatically excludes all Saturdays and Sundays.
- If
daysis "0", WORKDAY.INTL returns the value ofstart_dateunchanged. - WORKDAY.INTL ignores any time values.
- If any of the provided date(s) aren't valid, WORKDAY.INTL returns the #NUM! error.
- Wildcards don't work with this function.
Tips
- It's generally easier to put holiday dates in a cell range and reference that range.