Exceljet

Quick, clean, and to the point

Excel WORKDAY.INTL Function

Excel WORKDAY.INTL function
Summary 

The Excel WORKDAY.INTL function takes a date and returns the nearest working n days in the future or past, based on an offset value you provide. Unlike the WORKDAY function, WORKDAY.INTL allows you to customize which days are considered weekends (non-working days). You can use WORKDAY.INTL function to calculate completion dates, ship dates, etc. that take into account non-working days.

Purpose 
Get date n working days in future or past
Return value 
Next or previous working date based on inputs
Syntax 
=WORKDAY.INTL (start_date, days, [weekend], [holidays])
Arguments 
  • start_date - The start date.
  • days - The end date.
  • weekend - [optional] Setting for which days of the week should be considered weekends.
  • holidays - [optional] A list of one or more dates that should be considered non-work days.
Version 
Usage notes 

The WORKDAY.INTL function calculates the "nearest" working day n days in the past or future, taking into non-working days. WORKDAY.INTL can be used to calculate due dates, delivery dates, and other dates that must exclude non-working days. Use a positive number as days for future dates and a negative number for past dates. This function is more robust than the WORKDAY function because it lets you customize which days of the week are considered weekends.

The WORKDAY.INTL function takes four arguments: start_date, days, weekend, and holidaysStart_date must be a valid Excel date. The days argument is the number of days in the future or past to move from start_date, taking into account non-working days. Use a positive number for days to move forward in time, and a negative number to move back.

The weekend argument is optional and controls which day(s) of the week should be considered weekends, and therefore non-working days. By default, WORKDAY.INTL will treat Saturday and Sunday as non-working days. See below for details on how to configure weekend to treat different days of the week as non-working days.

Holidays is an optional argument to specify non-working days. Holidays should be provided as a range that contains valid Excel dates. If holidays are not provided, WORKDAY.INTL will treat only Saturdays and Sundays as non-working days, unless the weekend argument has been set differently.

Note: WORKDAY.INTL does not include the start date as a work day. 

Example #1 - basic usage

With the start_date in A1, days set to 1, and holidays in C1:C3:

=WORKDAY.INTL(A1,1) // default - Sat and Sun excluded
=WORKDAY.INTL(A1,1,11) // exclude Sun only
=WORKDAY.INTL(A1,1,11,C1:C3) // exclude Sun + dates in C1:C3

In the last two examples above, we use the numeric code 11 to set weekends to Mondays only. See the table below for the full list of available codes.

Example #2 - Weekend mask

Another (easier) way to specify weekend days is to use a 7-character "mask" to indicate weekends with ones and zeros.  In this scheme, a text string of 7 ones and zeros are provided to indicate weekends, were the first character is Monday, and the last character is Sunday. Use one (1) to indicate weekend, and zero (0) to indicate a working day. This method is more flexible, since it allows you to designate any day(s) of the week as a weekend (i.e. non-working days). For example:

=WORKDAY.INTL(A1,3,"0000000") // no weekends
=WORKDAY.INTL(A1,3,"1000000") // weekend = Mon
=WORKDAY.INTL(A1,3,"1100000") // weekend = Mon+Tue
=WORKDAY.INTL(A1,3,"1110000") // weekend = Mon+Tue+Wed
=WORKDAY.INTL(A1,3,"1010000") // weekend = Mon+Wed

Weekend codes

Use any of the codes below for the weekend argument to select a "fixed" weekend option.

Code Weekend days
1 (default) 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

Notes

  • If start_date is invalid, WORKDAY.INTL returns the #NUM! error.
  • If start_date + day is invalid, WORKDAY.INTL returns the #NUM! error.
  • If any holiday is invalid, WORKDAY.INTL returns the #NUM! error.
  • If weekend is invalid, WORKDAY.INTL returns the #VALUE! error.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.