Excel NETWORKDAYS.INTL Function
Basic Description
The Excel 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.
The syntax of the Networkdays.Intl function is:
Where the arguments are as follows:
| 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. These are 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 that Microsoft advises that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the start_date, end_date and [holidays] arguments for the Networkdays.Intl function should be input as either:
- References to cells containing dates
- Dates returned from other functions or formulas
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.
Networkdays.Intl Function Examples
The spreadsheets below show simple examples of the Excel Networkdays.Intl function calculating the number of work days between the two dates 01-Dec-2010 and 05-Jan-2011.
The format of the function is shown in the top spreadsheet and the results are shown below.
Formulas:
|
Results:
|
In the above spreadsheet :
- The holidays array has been omitted from the calculation in cell A8. Therefore this result excludes Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
- In the example in cell A9 the [weekend] argument is 1 (specifying weekends on Saturdays and Sundays) and the holidays array (in cells B3 - B5) is provided to the Networkdays.Intl function. Therefore the calculation excludes Saturdays and Sundays and the listed Christmas and New Year holidays.
- In the example in cell A10 Fridays, Saturdays and Sundays are specified as weekends, but no holiday array has been supplied to the function. Therefore the calculation excludes Fridays, Saturdays and Sundays but includes all other weekdays, including the holidays at Christmas and New Year.
Note also that, as recommended by Microsoft, in all three calls to the Networkdays.Intl function, the start_date, end_date, and [holidays] arguments have been supplied as cell references.
For further examples of the Excel Networkdays.Intl function, see the Microsoft Office website
Networkdays.Intl Function Errors
If you get an error from the Excel Networkdays.Intl function, this is likely to be one of the following:
| #NUM! | - | Occurs if the supplied [weekend] argument is invalid (see above explanation of this argument). |
| #VALUE! | - | Occurs if the supplied start_date, end_date, or any of the values in the supplied [holidays] array are not valid dates. |
