To generate the number of working days between any two dates, the Excel NETWORKDAYS.INTL function helps you in your calculation. The NETWORKDAYS.INTL function automatically removes Saturday and Sunday from working days. However, the function allows you to determine the days of the week that should be addressed as weekends.
=NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])
- start_date – the beginning date from which to begin the analysis.
- end_date – the last date where the analysis stops.
- weekend – this is a unique setting for which days should be addressed as weekends.
- holidays – this refers to dates that can be addressed as non-working days.
Note that the weekend and holiday arguments are optional parameters.
The NETWORKDAYS.INTL function is an extension of the NETWORKDAYS function. This is because it lets you decide which of the days should be weekends. The function also considers holidays, with Excel handling them as non-working days. As a result, set holidays do not show in your results.
When using the NETWORDAYS.INTL function in Excel, the following are used to represent certain inclusions and omissions in days of the week.
|1||Saturday & Sunday|
|2||Sunday & Monday|
|3||Monday & Tuesday|
|4||Tuesday & Wednesday|
|5||Wednesday & Thursday|
|6||Thursday & Friday|
|7||Friday & Saturday|
In this example, we would consider the number of working days between two dates (start date and end date).Example
Figure 1. Excel showing the end dates and start dates.
Enter the formula for the NETWORKDAYS.INTL function.
Using this formula means that the number of working days returned would include weekends (Saturday and Sunday). But the function excludes holidays from the results.
Figure 2. the NETWORKDAYS.INTL returns the number of working days excluding holidays.
In this step, the holiday argument is required. And to include it in our formula, we need to create a separate range for holidays.
Then, enter the formula,
The NETWORKDAYS.INTL function returns a result that includes weekends (Saturdays and Sundays), and removes the holidays that are in cells A20 and A21.
32, as shown in the image, is the number of workdays between these two dates.
Figure 3. The NETWORKDAYS.INTL returns the number of working days excluding holidays.
Masking workdays in NETWORKDAYS.INTL function
“Masks” can be used in excel by the NETWORKDAYS.INTL function to pick particular weekends in our weekend arguments. Each of these marks come as characters with 7 strings. The characters in these strings are only two – 1 and 0. Here, one represents a weekend, while zero represents a workday.
For instance, here are some interpretations of these strings:
- 0000100 – this means that there is only one weekend in the week and that happens to be a Friday.
- 1010101- this shows that there are only three workdays in the week, including, Monday, Wednesday, and Friday.
- 0001100 – here, only Thursday and Friday can be considered as weekends.
- 1111100 – there are only two workdays in this instance. These are just Friday and Saturday.
Notes on Excel NETWORKDAYS.INTL function
- The function returns the #VALUE! Error, when the weekend value supplied, is not valid.
- The NETWORKDAYS.INTL function returns a negative value if the start date supplied is bigger than the end date given.
- You get the #NUM! error, when both dates given, are completely out of range.