Date and time
Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days
and which are holidays
Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
Don't let the "INTL" in the name of this function mislead
you. It really has nothing to do with international business.
Instead, NETWORKDAYS.INTL is the most powerful and flexible function
that Excel offers for counting the number of workdays in a
specified period. You can easily specify a weekly work schedule
and include any number of holidays in the calculation.
NETWORKDAYS.INTL(start_date, end_date, weekend, holidays)
The starting date for which the difference is to be
Required. The ending date for which the difference is to be
Optional. This argument can take two forms. First, it can be
a number that specifies one of seventeen weekend
combinations, as shown in the Remarks below. Second, it can
be a seven-character string of ones and zeros that specifies
which days of the week are weekend days, starting with Monday.
"1111100" indicates that
Saturday and Sunday are weekend days.
"1100110" indicates that
Wednesday, Thursday, and Sunday are weekend days.
Optional. A list of one or more dates that are excluded from
the workday calendar. This argument can be a range of dates
in a worksheet or an array constant. The sequence of dates
in the list doesn't matter.
Excel 2007 and above. (Excel 2010 introduced the ability to
specify weekends using a seven-character string of ones and
The following table shows the values you can use to specify
when the weekend for the NETWORKDAYS.INTL calculation. However,
if you wish to specify a weekend other than Saturday or Sunday,
and if you use Excel 2010 or above, the alternate method is
preferred. That is, use the seven-character string, as explained
in weekend above and illustrated in the
Examples below. Doing so better documents your intentions and is
easier to modify.
Weekend Days Specified|
|1 or omitted||Saturday, Sunday|
download this example workbook here,
along with all other example workbooks I've completed for this
Excel help area.
This example shows three correct ways to
hard-code the dates for this function, and one way to do it
Versions 1 and 2 show that Excel will
convert date text into a date serial number, and then return the
correct result. The date text can be in any format that the
DATEVALUE function will
Version 3 shows that you also can use
the DATE function to specify the
date serial number directly.
Version 4 shows that you can not
specify the date using what appears to be a reasonable
method. Here, Excel treats those arguments as a series of
instructions to divide the numbers shown, not to represent a
The weekend value of 11, as shown in the table
in Remarks above, specifies that only Sunday is treated as a
holiday. With this schedule, you'd work 313 days in 2012.
The weekend value of
"1100110" specifies that you treat Wednesday, Thursday, and
Sunday as weekends.
By adding three holidays to the
calculation, we reduce the number of workdays by only two days.
This is because December 25 fell on Wednesday in 2013, a day
we've already specified as part of our weekend.
You also can specify the Holiday
arguments using an array constant. Here, the first version uses
date serial numbers and the second uses date text.