For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Date and time

NETWORKDAYS.INTL Function

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


by 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.

Syntax

NETWORKDAYS.INTL(start_date, end_date, weekend, holidays)

  • start_date  Required. The starting date for which the difference is to be calculated.

  • end_date  Required. The ending date for which the difference is to be calculated.

  • weekend  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. To illustrate..

    • "1111100" indicates that Saturday and Sunday are weekend days.

    • "1100110" indicates that Wednesday, Thursday, and Sunday are weekend days.

  • holidays  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.

Applies To

Excel 2007 and above. (Excel 2010 introduced the ability to specify weekends using a seven-character string of ones and zeros.)

Remarks

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 value Weekend Days Specified
1 or omittedSaturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday only

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Example 1:

networkdays.intl example 1

Example 2:

networkdays.intl example 2

This example shows three correct ways to hard-code the dates for this function, and one way to do it wrong.

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 convert.

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 date.

Example 3:

networkdays.intl example 3

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.

Example 4:

networkdays.intl example 4

The weekend value of "1100110" specifies that you treat Wednesday, Thursday, and Sunday as weekends.

Example 5:

networkdays.intl example 5

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.

Example 6:

networkdays.intl example 6

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.

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards