WORKDAY.INTL Function

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 holidays. Start_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.

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.