Related Functions:

The WORKDAY.INTL function returns a date that is a supplied number of working days (excluding weekends and holidays) ahead of a given start date. The function allows the user to specify which days are counted as weekends.

  • The function is new in Excel 2010 and so is not available in earlier versions of Excel. However, it is similar to the WORKDAY function, which is available in earlier versions of Excel.

Syntax

=WORKDAY.INTL(start_date,days,[weekend],[holidays])

Arguments

Argument Description
start_date The initial date, from which to count the number of workdays
days The number of workdays to add onto start_date
[weekend] Optional. Indicates the days of the week that are weekend days and are not considered working days

  [weekend] Description
  1 or omitted  Saturday and Sunday (default)
  2 Sunday and Monday
  3 Monday and Tuesday
  4 Tuesday and Wednesday
  5 Wednesday and Thursday
  6 Thursday and Friday
  7 Friday and Saturday
  11 Sunday only
  12 Monday only
  13 Tuesday only
  14 Wednesday only
  15 Thursday only
  16 Friday only
  17 Saturday only
[holidays] Optional. A set of one or more dates that are to be excluded from the working day calendar. Holidays shall be a range of cells that contain the dates, or an array constant of the serial values that represent those dates

Note: the start_date and [holidays] arguments should be input as either:

  • References to cells containing dates, or
  • Dates returned from formulas

If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Examples

  A B C D
1 Data      
2 Mon 22-Dec-2014 Start date    
3 45 Days to completion    
4 Holidays      
5 Thu 25-Dec-2014 Christmas    
6 Fri 26-Dec-2014 Boxing Day    
7 Wed 31-Dec-2014 New Years Eve    
8 Thu 01-Jan-2015 New Years Day    
9        
10 Formula Result Notes
11 =WORKDAY.INTL(A2,45) 42058 The integer representing the date 45 days after start date; no weekends or holidays (2/23/2015)
12 =WORKDAY.INTL(A2,A3,1,A5:A8) 42062 The integer representing the date 45 days after start date; weekends include Sat and Sun; no holidays (2/27/2015)
13 =WORKDAY.INTL(A2,A3,”0000111″) 42037 The integer representing the date 45 days after start date; no holidays; weekends include Fri, Sat & Sun (3/10/2015)
14 =TEXT(WORKDAY.INTL(A2,A3,1,A5:A8),”m/dd/yyy”) 2/27/2015 Uses the TEXT function to format the resulting serial number (42062) in a “m/dd/yyyy” format

Notes on [weekend]

Possible string values for the [weekend] argument consist of a series of seven 0’s and 1’s which represent the seven weekdays, starting from Monday. 

  • “1” denotes a day that should be counted as a weekend
  • “0” represents a working day

For example:

0000100 denotes Fridays only counted as weekend days
0001100 denotes Thursdays and Fridays counted as weekend days
0000111 denotes Fridays, Saturdays and Sundays counted as weekend days
  • The string “1111111” is not valid.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if:

  The start_date plus the supplied days argument results in an invalid date
  The [weekend] argument is an invalid text string
  The days argument is non-numeric
#NUM! Occurs if:

  The start_date plus the days argument results in an invalid date
  The supplied [weekend] argument is an invalid numeric value