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_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.

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!