What is the NETWORKDAYS.INTL Function?
The NETWORKDAYS.INTL Function helps in calculating the number of workdays between two given dates and thus works as a business days calculator. It is a built-in function and is categorized as a Date/Time Function. The function was introduced in MS Excel 2010.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
The NETWORKDAYS.INTL function includes the following arguments:
- Start_date (required argument) – The start_date will either be earlier than end_date or later than end_date or the same as end_date.
- End_date (required argument) – The end date.
- Weekend (optional argument) – If we omit the weekend parameter, it will assume that the weekends will contain Sunday & Saturday. The function excludes Saturday and Sunday by default, but it provides a way for the user to specify which days of the week are considered weekends.
The following are the weekend number values that indicate which weekend days the function will take:
|Weekend Number||Weekend Days|
|1 or omitted||Saturday, Sunday|
Weekend string values are seven characters long and each character in the string represents a day of the week. It starts on Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. For example, 0000110 would result in a weekend that is Friday and Saturday.
- Holidays (optional argument) – It specifies the list of holidays that should be excluded from the work days calculation. We can enter it as a range of cells that contain the holiday dates (that is F2:F4) or as a list of serial numbers that represent the holiday dates.
How to use NETWORKDAYS.INTL Function in Excel?
Let’s see a few examples to understand how the function works.
Example 1 (Business days calculator)
Let’s say we wish to calculate the workdays or business days from January 11, 2017 to January 31, 2018. The holidays in between are:
- November 23, 2017 – Thanksgiving Day
- December 25, 2017 – Christmas
- January 1, 2018 – New Year
Now if I consider that the weekend falls on Saturday and Sunday, using the start date, end date and holidays given, I can calculate the number of working days.
The formula to use would be:
Using this formula, we will get the number of working days as 273.
We can change the weekends according to our requirements by providing the values given above.
Example 2 (Business days calculator)
When calculating past working days, it will give a negative result.
The dates given are February 28, 2016 as start date and January 31, 2016 as the end date. There are no holidays in between. However, the weekend comes on Friday and Saturday. So, the formula to be used would be =NETWORKDAYS.INTL(A2,B2,7).
The result we got is -21, that is, 21 past days.
Example 3 (Business days calculator)
Continuing with Example 1, let’s assume that the weekend falls on Thursday. The formula to be used would be =NETWORKDAYS.INTL(A2,B2,15,D2:D4).
So, the function gave us the result of 329 days as weekends fall on Thursday, and Thanksgiving is on Thursday.
Notes about the NETWORKDAYS.INTL Function
- Negative return value – If the start date is later than the end date, the function will return a negative value.
- #NUM! error – Occurs when start_date or end_date are outside the range of the current date base value.
- #VALUE! error – When the weekend string contains invalid characters or is of invalid length, we will get this error.
Thanks for reading CFI’s guide to important Excel functions and how to make a business days calculator in Excel! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources: