NETWORKDAYS Function
Returns the number of working days between two given dates
What is the NETWORKDAYS function in Excel?
NETWORKDAYS Function helps you figure out the number of working days between two dates, taking into account holidays.
In our daily tasks, we often encounter challenges that require smart solutions, and we sometimes take decisions that we believe might be shortcuts, but in reality, they increase our workflow. Imagine a scenario where an intern at WallStreetOasis is asked to calculate the working days between X and Y dates. As I passed by, I saw him looking out the window blankly.
I asked him, “How’s your day, Jacob? What are you up to today?”
He said,” All good! Just trying to calculate how many working days fall between 14th January 2023 and 23rd May 2023, wherein we have holidays on five different occasions. I need a calendar, though….”
I was surprised because I knew he was good at Excel, yet he did not understand that a function that specifically let him do the same task with a single formula existed!
If you've ever found yourself in a similar situation or want to enhance your Excel skills, this article is for you. We'll explore how to calculate working days between two dates efficiently, leveraging the power of the Excel NETWORKDAYS function. Let's dive in!
Key Takeaways
- The NETWORKDAYS function can calculate the working days between two dates in Excel.
- The function accepts three different arguments - start_date, end_date, and the different holidays that might occur on work days.
- The function ignores Saturday & Sunday by default in calculating working days.
- The function NETWORKDAYS.INTL allows users to customize the weekend days from the default Saturday & Sunday.
- If the objective is not to find the working days but rather just the difference between two days in terms of days, months, or even years, then we can use the DATEDIF function.
NETWORKDAYS Function in Excel
The NETWORKDAYS is categorized as a Date & Time function that returns the number of working days between two dates.
For example, if the date A is 14th January 2023 and date B is 20th January 2023, the function will return the number of working days between them as 5.
If we delve deeper into the example, we find that the 14th and 15th of January were the weekend, i.e., Saturday-Sunday, whereas, from the 16th, the working week started. Thus, from 16th to 20th, i.e., Monday to Friday, we get the total number of working days as 5.
Syntax
where,
- start_date - (required) the starting date
- end_date - (required) the ending date
- holidays - (optional) list of holidays that must be excluded from the workday calculations.
Let's see an example of how to use the function in Excel.
NETWORKDAYS Function Example in Excel
You may think of using the function now that you know its syntax. There are, again, two methods you can follow to use the process in Excel.
One is to reference the cell that contains the date value, and another is to use the DATE function nested inside so that one does not make the rookie mistake of misrepresenting the date in the formula.
The latter method is helpful for someone who has just started their Excel journey; however, we will focus more on the former method.
Suppose you have the data as illustrated below: Our first assumption with the example is that we do not have any holidays between those dates. Therefore, the formula to calculate the working days will be =NETWORKDAYS(B3,C3), which gives the result as
Thus, working days between 26th February and 30th April equals 45. Let's assume there are two public holidays between those dates, which fall on 22nd March and 23rd March 2023.
The data looks as illustrated below:
The formula will be =NETWORKDAYS(B3,C3,D3:D4), giving the result as 43.
Since both the dates, 22nd and 23rd March, fall on working days; Excel excludes them from the working day's calculation. As a result, the total number of working days returned by Excel is equal to 43.
Had those two days fallen on the weekend, i.e., Saturday & Sunday, there would have been no difference in the number of working days, i.e., it would still be equal to 45.
NETWORKDAYS.INTL function
If you work at an organization with a custom working week, say from Tuesday to Saturday, with off on Sunday and Monday, then NETWORKDAYS.INTL makes a lot of sense to you.
The function has an additional argument that allows the user to customize the working week to calculate the working days between two given dates.
where,
- start_date - (required) the starting date in the calculation of working days
- end_date - (required) the ending date in the calculation of working days
- weekend - (optional) determines what the weekend days would be
- holidays - (optional) list of holidays that would be excluded from the working day's calculation
The different types of the weekend argument you can select for the function are:
Thus, the function enables users to set up two weekend days using the argument from 1 to 7 and a single weekend day from argument 11 to 17.
Suppose the organization has a five-day workweek from Saturday to Wednesday. They need to calculate the working days between the two given dates, as illustrated below:
The organization has Thursday & Friday as the weekend. Hence, we need to select the weekend argument accordingly. If we check the list, we find that the corresponding value for Thursday & Friday equals 6.
Substituting the values in the formula =NETWORKDAYS.INTL(B3,C3,6,D3:D4), we get
Based on the working week, we find 45 working days despite two other holidays that fall on the 22nd & 23rd of March. Thus, changing the workweek can entirely change the dynamics of the results obtained by NETWORKDAYS.INTL function.
DATEDIF function
If another function works similarly to the NETWORKDAYS, the DATEDIF function has now been changed to compatibility status.
The only difference is that the latter can't calculate the working days, excluding the weekend days. The function can only estimate how many days separate the two given dates.
It can also further find the difference between the dates in days, months, and even years.
You won't be able to find the function in the library; to use it, you need to type in the formula as illustrated above.
- start_date - (required) the starting date
- end_date - (required) the ending date
- unit - (required) information type, which will be returned. The argument can accept different values, as illustrated below:
| Unit | Result |
|---|---|
| "Y" | Returns the result in terms of year |
| "M" | Returns the result in terms of months |
| "D" | Returns the result in terms of days |
| "MD" | Ignores the month & year to return the difference between the days in start_date & end_date |
| "YM" | Ignores the day & year to return the difference between the months in start_date & end_date |
| "YD" | Ignores the month & day to return the difference between the years in start_date & end_date |
Suppose you have the data as illustrated below:
To find the difference between the dates in terms of years, we will use the formula =DATEDIF(B3,C3, "Y") in cell D3, which gives
Since both the dates fall in the same year, we get the result as zero. Similarly, we will use the formula =DATEDIF(B3,C3,"M") in cell E3 and =DATEDIF(B3,C3,"D") in cell F3, which gives the result:
As stated earlier, the function even includes the weekends in calculating the difference between two days. It might not help to estimate the working days between two given days.
However, finding the difference between two normal dates can be extremely useful.
or Want to Sign up with your social account?