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 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.
Usage notes 

WORKDAY.INTL figures out a date that represents the "nearest" working day N days in the past or future. 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.

Example formulas

D4=WORKDAY.INTL(B4,C4) // default - weekends excluded
D5=WORKDAY.INTL(B5,C5,11) // weekend set to Sunday only
D6=WORKDAY.INTL(B6,C6,1,B9:B9) // default weekend with holidays

Weekends

By default, WORKDAY.INTL will exclude weekends (Saturday and Sunday) . However, you can control which days are considered weekends, by supplying a code from the table below for the weekend argument.

Another (easier) way to specify weekend days is to use a "mask" to indicate weekends with ones and zeros.  In this scheme, a 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 of the week as a weekend (i.e. non-working day). 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

Holidays

WORKDAY.INTL can also optionally take into account holidays. For the holidays argument, supply a range that contains holiday dates. These dates are treated as non-working days and will not be included in the result.

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.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.