Home » Excel-Built-In-Functions » Excel-Date-And-Time-Functions » Excel-Networkdays-Intl-Function
Excel NETWORKDAYS.INTL Function
Function Description
The Excel Networkdays.Intl function calculates the number of whole work days between two supplied dates, excluding weekends and holidays. The function allows the user to specify which days are counted as weekends and holidays.
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:
NETWORKDAYS.INTL( start_date, end_date, [weekend], [holidays] )
Where the arguments are as follows:
| start_date | - | The start date, from which to count the number of workdays. (The start_date is counted in the returned number of days). |
| end_date | - | The end date, to count the number of workdays up to. (The end_date is counted in the returned number of days). |
| [weekend] | - | An optional argument, which specifies which weekdays should be counted as the weekend. This can be either a number or a string, as explained below: |
Possible number values for the [weekend] argument are: | [weekend] | days counted
as weekend | 1
(or omitted) | Sat & Sun | | 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. Each 1 denotes a day that should be counted as a weekend and each 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. |
|
| [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
or- Dates returned from formulas.
Networkdays.Intl Function Examples
The spreadsheets below show three examples of the Excel Networkdays.Intl function used to calculate the number of work days between the two dates Dec 01, 2015 and Jan 15, 2016. In each case, the days to be counted as weekends and holidays are different.
Formulas: | | A | B |
|---|
| 1 | Start Date: | 12/01/2015 |
|---|
| 2 | End Date: | 01/15/2016 |
|---|
| 3 | Holidays: | 12/25/2015 |
|---|
| 4 | | 12/28/2015 |
|---|
| 5 | | 01/01/2016 |
|---|
| 6 | | |
|---|
| 7 | Work days between Dec 1st, 2015 & Jan 15th, 2016: |
|---|
| 8 | =NETWORKDAYS.INTL( B1, B2 ) |
|---|
| 9 | =NETWORKDAYS.INTL( B1, B2, 1, B3:B5 ) |
|---|
| 10 | =NETWORKDAYS.INTL( B1, B2, "0000111" ) |
|---|
|
Results: | | A | B |
|---|
| 7 | Work days between Dec 1st, 2015 & Jan 15th, 2016: |
|---|
| 8 | 34 | |
|---|
| 9 | 31 | |
|---|
| 10 | 27 | |
|---|
|
Note that, in the above spreadsheets:
- 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 cell A9 of the example spreadsheet, 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.
See the Microsoft Office website for further information and examples of the Excel Networkdays.Intl function.
Networkdays.Intl Function Errors
If you get an error from the Excel Networkdays.Intl function, this is likely to be one of the following:
Common Errors
| #NUM! | - | Occurs if the supplied [weekend] argument is an invalid numeric value. |
| #VALUE! | - | Occurs if either: - The supplied start_date, end_date, or any of the values in the supplied [holidays] array are not valid dates;
- The supplied [weekend] argument is an invalid text string;
|