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 you can use WORKDAY.INTL to get the next working day like this:

=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 + holidays in C1:C3

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

Example #2 - code string for weekends

One of WORKDAY.INTL's tricks is that it can be configured to treat any day of the week as a workday by providing the weekend argument as a 7-digit code that covers all seven days of the week, Monday through Saturday. In this scheme, a 1 indicates a weekend (non-working) day, and a 0 indicates a workday. The table below shows sample weekend codes in column B and the workdays they define in column J:

This method is more flexible since it can define any day of the week as a weekend or workday. For example, to get the next workday that is a Monday, Wednesday, or Friday after a date in cell A1, you can use a formula like this:

=WORKDAY.INTL(A1,1,"0101011") // Mon, Wed, Fri

To get the next Tuesday or Thursday, you can use a formula like this:

=WORKDAY.INTL(A1,1,"1010111") // Tue, Thu

You can use this feature to create a list of weekends only, or a list of sequential Mondays, Wednesdays, and Fridays, or any other combination of weekdays, so long as the pattern repeats each week.

Note: weekend must be entered as a text string surrounded by double quotes ("") when using this feature.

Weekend codes

The table below shows the codes available for the weekend argument to select a "fixed" weekend option. Note that unlike the "code string" option explained above, these codes are numeric and should not be entered as text.

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.