Related Functions:

The NETWORKDAYS.INTL function calculates the number of whole work days between two supplied dates. This excludes all weekends and a supplied list of holidays. The function allows the user to specify which days are counted as weekends.

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

Syntax

=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

Arguments

Argument Description
start_date The start of the period for which we are counting days (the start date is counted in the returned number of days)
end_date The end of the period for which we are counting days (the end date is counted in the returned number of days)
[weekend] An optional argument, which specifies which weekdays should be counted as weekends. This can be either a number or a string. The [weekend] argument is explained below.
[holidays] An optional argument, which specifies an array of dates (in addition to weekends) that are not to be counted as working days.

Note: The start_date, end_date and [holidays] arguments for the NETWORKDAYS.INTL function should be input as either:

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

If date arguments are entered as text, Excel may misinterpret them, due to different date interpretations by different systems or 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 01-Dec-2010 Start date    
3 Mon 05-Jan-2011 End date    
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 =NETWORKDAYS.INTL(A2,A3) 26 No holidays; weekends on Sat & Sun
12 =NETWORKDAYS.INTL(A2,A3,1,A5:A8) 22 Excluding holidays in B7-B10; weekends on Sat & Sun
14 =NETWORKDAYS.INTL(A1:B1,”0000111″) 21 No holidays; weekends on Fri, Sat & Sun
Weekend Explanation

Possible number values for the [weekend] argument are:

[weekend] days counted as weekend
1 (or omitted) Sat & Sun (default)
2 Sun & Mon
3 Mon & Tue
4 Tue & Wed
5 Wed & Thu
6 Thu & Fri
7 Fri & Sat
11 Sunday only
12 Monday only
13 Tuesday only
14 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only

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, end_date, or supplied [holidays] dates are not valid dates
#NUM! Occurs if the supplied [weekend] argument is an invalid numeric value