WORKDAY.INTL Function

The WORKDAY.INTL function calculates a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. You can WORKDAY.INTL to calculate project start dates, delivery dates, and completion dates that must ignore non-working days. The WORKDAY.INTL function is more robust than the simpler WORKDAY function because weekend days can be customized so that any day of the week can be a workday or non-workday. Note that WORKDAY.INTL will automatically exclude Saturdays and Sundays but will only exclude holidays if they are provided.

The WORKDAY.INTL function takes four arguments:

• start_date - the date from which to start counting. Note that WORKDAY.INTL does not include the start date as a work day.
• days - the number of days in the future or past to calculate a workday. Use a positive number for days to calculate dates in the future, and a negative number for past dates.
• weekend - an optional argument that controls which days of the week are working and non-working days. If weekend is omitted, WORKDAY.INTL will treat Saturdays and Sundays as non-working days by default. The weekend argument can be provided as a numeric code or a text string like "0000011". See below for details.
• holidays - an optional argument to provide non-working dates that should be skipped when computing a result. Holidays must be provided as a range or array that contains valid Excel dates.

The WORKDAY.INTL function explained

To illustrate how WORKDAY.INTL works, assume we are scheduling a task that takes 5 working days, starting on Monday, July 1, 2024. The goal is to calculate a date that is 5 working days after July 1, 2024. Beginning with the simplest case, let's just add 5 days to the start date:

="1-Jul-2024"+5 // returns "6-Jul-2024"

The result is Saturday, July 6, 2024. While this is a valid result, it doesn't take into account that Saturday is probably not a working day. If, on the other hand, we use WORKDAY.INTL to calculate a date 5 days after July 1:

=WORKDAY.INTL("1-Jul-2024",5) // returns "8-Jul-2024"

The result is Monday, July 8, 2024. This is because WORKDAY.INTL automatically skips Saturdays and Sundays when it calculates a result. Taking things one step further, what if we need to schedule based on a 4-day workweek, where the workdays are Monday through Thursday? In that case, we can extend the formula with the optional weekend argument like this:

=WORKDAY.INTL("1-Jul-2024",5,"0000111") // returns "9-Jul-2024"

The result is now Tuesday, July 9. The text string "0000111" means Mondays, Tuesdays, Wednesdays, and Thursdays are workdays, and Fridays, Saturdays, and Sundays are "weekend days" (i.e. non-working days). Finally, let's extend the formula one more time and provide two holidays:

=WORKDAY.INTL("1-Jul-2024",5,"0000111",{"4-Jul-2024";"2-Sep-2024"}) // returns "10-Jul-2024"

Since one of the holidays (July 4, 2024) overlaps schedule, WORKDAY.INTL now returns Tuesday, Wednesday, July 10, 2024, since Thursday, July 4, 2024 is also skipped in the calculation.

Note: the holidays above are provided as an array constant but more typically holidays are provided as a range. Remember that holidays must be valid Excel dates. The name of the holiday is not used at all by WORKDAY.INTL.

Of course, in real life, you will not hardcode dates into formulas like this. You will instead use cell references. The screen below shows the four formulas above "ported" to a workbook with cell references. Notice the final calculated date moves farther into the future as we restrict the schedule:

The formula in D5 does not use WORKDAY.INTL and simply adds 5 days to the start date:

=B5+C5 // returns "6-Jul-2024"

The formula in D6 uses the WORKDAY.INTL function but does not provide any holidays:

=WORKDAY.INTL(B6,C6)// returns "8-Jul-2024"

The formula in D7 implements a 4-day workweek by providing "0000111" for the weekend argument:

=WORKDAY.INTL(B7,C7,"0000111") // returns "9-Jul-2024"

Finally, the formula in D8 sets the same 4-day workweek and provides a small list of holidays in B13:B14:

=WORKDAY.INTL(B7,C7,"0000111",B13:B14) // returns "10-Jul-2024"

In all cases, the start_date in column B and the days in column D are the same.

The weekend argument

What makes WORKDAY.INTL different from the original WORKDAY function is the weekend argument. Whereas the WORKDAY function is hardcoded to treat Saturday and Sunday as weekend (i.e. non-working) days the weekend argument in WORKDAY.INTL can be configured to specify any day of the week as a working or non-working day.

There are two ways to configure the weekend argument:

1. Use a numeric code to select from a pre-configured list of working and non-working days
2. Provide a 7-digit code string that provides a setting for every day of the week.

Let's look at both approaches.

Numeric code for weekend

The first way to provide a value for weekend is to provide a numeric code from the table below, which contains 14 preconfigured options. For example, the generic formulas below show how WORKDAY.INTL can be configured to find the "next" working day with three different workweeks:

1. A standard 5-day workweek with Saturday and Sunday as weekend days (1, the default)
2. A 5-day workweek with Sunday and Monday as weekend days (2)
3. A 6-day workweek with Sundays only as a weekend (11)

=WORKDAY.INTL(A1,1,1) // Saturday and Sunday (default)
=WORKDAY.INTL(A1,1,2) // Sunday and Monday
=WORKDAY.INTL(A1,1,11) // Sunday only

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. Note that unlike the "code string" option explained below, these codes are numeric and should not be entered as text. For simplicity, none of the formulas above provided holidays, but they can be added as the fourth argument.

Code string for weekends

The second way to provide a value for weekend is to provide 7-digit text string that covers all seven days of the week, Monday through Saturday. This text string can contain only 1s and 0s. 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:

In the image above, shaded cells indicate non-working days and unshaded cells are working days.

The most confusing thing about this method is that you need to "think backwards": you are not marking working days with a 1, you are marking non-working days with a 1. The zeros are working days. For example, to specify a 6-day workweek with Sunday only as a nonworking day, you would provide "0000001" (row 8 in the worksheet above):

=WORKDAY.INTL(A1,1,"0000001")

To get the next workday that is a Monday, Wednesday, or Friday, you can use a formula like this:

=WORKDAY.INTL(A1,1,"0101011")

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

=WORKDAY.INTL(A1,1,"1010111")

You can use this same 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 (i.e."0000011") when using this feature. Personally, I prefer the this second approach because it can handle any combination of working/non-working days and you don't need to look up an arbitrary numeric code.

Worksheet Example

In the worksheet below, Column B contains a variety of different start dates, column C contains the number of days to use, and "holidays" is the named range F5:F13:

The formula in cell D5 is:

=WORKDAY.INTL(B5,C5,11,holidays)

• start_date - January 1, 2021 (B5)
• days - 1, (C5)
• weekend - 11 (numeric code for Sunday-only weekend)
• holidays - holidays (the named range F5:F13)

As the formula is copied down, WORKDAY.INTL calculates a date n working days from the start date using the value in column C for days. When calculating a result, WORKDAY.INTL excludes dates that are Sundays and dates that are holidays.

Note: named ranges automatically behave like absolute references so there is no need to lock the reference to "holidays" before copying the formula. If you prefer not to use a named range, use an absolute reference like $F$5:$F$13 instead.

Visualized Example

It can be hard to visualize how WORKDAY.INTL works when it calculates a result. The worksheet below contains a more detailed example that shows non-working days shaded in gray in columns D and E:

In the example above, WORKDAY.INTL is used to calculate a date that is 4 working days after 23-Dec-2024. The weekend argument is provided as "0000111, " specifying a 4-day workweek where Fridays, Saturdays, and Sundays are non-working days. The formulas in G5 and G6 show the result with and without the holidays in B11:B13:

=WORKDAY.INTL(start,days,"0000111")
=WORKDAY.INTL(start,days,"0000111",holidays)

The first formula (G5) excludes weekend days only and returns December 30, 2024. The second formula (G6) excludes weekend days and holidays and returns January 2, 2025. The dates in columns D and E are not required in this solution, they exist only to help visualize how the WORKDAY.INTL function evaluates working and non-working days and arrives at a final result. The shading and highlighting are applied with conditional formatting. For a full explanation with details, see this page.

Example - is this date a workday?

One problem you might run into is how to test a date to determine whether it is a workday. You can use WORKDAY.INTL for this task, but the formula is not immediately obvious. Essentially, we need to "trick" WORKDAY.INTL into evaluating a given date by shifting the date back one day and then asking for the next workday. You can see this approach in the worksheet below. The formula in cell D5 is:

=WORKDAY.INTL(B5-1,1,"0000111",holidays)=B5

For a more detailed explanation, see this example: Date is Workday.

Recommendations

• Use cell references for the start date, days, and holidays to make it easy to adjust the output quickly.
• Using the code string format for weekend (e.g. "0000111") is more flexible than a numeric code because you can make any day of the week a workday or non-workday.
• WORKDAY.INTL returns a date. If you need to calculate the number of working days between two dates, see the NETWORKDAYS function or the more flexible NETWORKDAYS.INTL function.

Notes

• WORKDAY.INTL returns a date that is a given number of working days from a specified start_date
• WORKDAY.INTL does not include the start date as a work day.
• Use a positive number for days to calculate dates in the future, and a negative number for past dates.
• If any argument is invalid, WORKDAY.INTL will return #NUM! or #VALUE!, depending on the input.
• If days is zero, WORKDAY.INTL will return the start_date unchanged.
• The weekend argument can be provided as a numeric code or a text string like "0000011"
• Holidays must be provided as valid Excel dates in a range or array.