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

WORKDAY.INTL(start_date, days, [weekend], [holidays])

Arguments

start_dateThe start date to add or subtract days
daysThe 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-numberWeekend days
1 or omittedSaturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday 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.

Future date: =WORKDAY.INTL("1/28/2021",5)

Past date: =WORKDAY.INTL("1/28/2021",-5)

WORKDAY.INTL Function 01

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

Sunday only: =WORKDAY.INTL("1/28/2021",5,11)

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

=WORKDAY.INTL(B22,C22,D22,B27:C27)

WORKDAY.INTL Function

Download Workbook

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!