The WORKDAY.INTL is a Date & Time function that can add or subtract the number of workdays from a given date. As the name suggests, the function is a modified version of the WORKDAY function which you can incorporate weekends and holidays in your calculations. In this guide, we’re going to show you how to use the WORKDAY.INTL function and also go over some tips and error handling methods. WORKDAY INTL Function
Supported versions
- Excel 2010 and newer
Syntax of WORKDAY INTL Function
Arguments
| start_date | The start date to add or subtract days |
| days | The number of dates before or after the start_date. |
| [weekend] | Optional. A predefined number or 7-character string which specifies the weekend day(s). Default is Saturday and Sunday. |
| [holidays] | Optional. A range of date values to be excluded, aside from the weekends. An array constant can be used for this parameter. |
Weekend
Predefined weekend days
Enter a predefined number from the list below to set corresponding days as weekend day(s).
| weekend-number | Weekend days |
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Custom weekend days
Alternatively, you can supply a 7-character length string containing 1 and 0 values only. Each character represents a day Monday through Sunday. Use 1 for weekend days and 0 for workdays.
For example, “0000011” is for setting Saturday and Sunday as weekend days. Or, “1000101” would mean a week which weekend days are “Monday”, “Friday” and “Sunday”.
Examples of WORKDAY INTL Function
Only weekends excluded
To calculate the date before or after a certain date (excluding the weekends), begin by providing the start_date and days arguments into the formula. Both date values can be a string, a serial number, or a calculated date by a formula (e.g. DATE).
In the following examples, we are adding 5 days to the start date, and also subtract 5 days from it. If it wasn't for the WORKDAY formula, we would get 2/2/2021 and 1/23/2021 instead. Since the WORKDAY.INTL function is not taking weekends into consideration in this calculation. we get see 2/4/2021 and 1/23/2021.
Past date: =WORKDAY.INTL("1/28/2021",-5)
Custom weekend days
Enter a value for the [weekend] argument to change the default Saturday-Sunday type weekend. You can either supply a number between 1 and 17, or a 7-character string to determine which days will be weekend days. WORKDAY INTL Function
Weekends and holidays excluded
You can provide an array of dates as [holidays] arguments to exclude specific dates with weekends. The [holiday] argument can be a range of cells or a constant array. The following example demonstrates scenarios both excluding and including holiday dates. WORKDAY INTL Function
Tips
- You can use WORKDAY function if your default weekends are Saturday-Sunday.
- WORKDAY.INTL function ignores time value of dates.
- Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.
Issues
- If text arguments are not valid dates, the WORKDAY.INTL function returns #VALUE! error value.
- Use the DATE function instead.INTL Fun
- Invalid [weekend] string, for example “111”, returns #NUM!
- Invalid [weekend] number, for example 111, returns #NUM!
