Rifat Hassan
In this guided session, we will show how you can calculate the total number of workdays between two dates using NETWORKDAYS.INTL function of Excel. Sometimes, you need to determine the amount of time that employees have accumulated over time. Moreover, you might have to define the weekend and holidays based on your requirements. All things are incorporated in the NETWORKDAYS.INTL function. Let’s explore the article to explore the function extensively.
The overview of the NETWORKDAYS.INTL function is shown in the screenshot up top. To be clear, you will discover more about the applications of the NETWORKDAYS.INTL function in the following sections.
Download Practice Workbook
Excel NETWORKDAYS.INTL Function: Syntax & Arguments
- Summary
Returns the number of whole workdays between 2 dates with custom weekend parameters.
- Syntax
=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])- Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| start_date | Required | The date from which you are counting the workdays. |
| end_date | Required | The date at which you want to stop counting. |
| [weekend] | Optional | A number that denotes which days of the week you want to have as weekends. |
| [holidays] | Optional | A list of all the holidays of the year. |
Note:
- Here, the NETWORK.INTL function counts both the start_date and the end_date.
- Additionally, there is a universally accepted table for the argument [weekend]. It has a number specified for each possible weekend. You have to input the specified number from the table that matches the weekend in your region.
| Weekend Number | Weekend Days |
|---|---|
| 1 | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday Only |
| 12 | Monday Only |
| 13 | Tuesday Only |
| 14 | Wednesday Only |
| 15 | Thursday Only |
| 16 | Friday Only |
| 17 | Saturday Only |
- If your region has Saturday and Sunday as weekends, you have to input 1, if Sunday and Monday are weekends, then you have to input 2, and so on. This is optional. The default is 1 (Saturday and Sunday).
- [holidays] is a list of all the holidays in a year. You have to input this as an Excel Array. Remember, the years in the list of holidays do not matter. For example, if you have a holiday on 14-Feb-2020 but want to count the workdays in the year 2019, it will still count 14-Feb as a holiday. This is also optional. The default is an empty array.
- Return Value
Returns the total number of workdays between the start_date and the end_date, including both the start_date and the end_date, but excluding the specified [weekend] each week and the [holidays] in between.
4 Practical Examples of Using the NETWORKDAYS.INTL Function in Excel
Let’s begin with the data set below. We have the Employee Names, Starting Date, and Ending Date of five employees of a company named Sunshine Group.
Then, we want to count the number of Total Workdays of each employee. To do that, we first have to create a list of all the holidays.
See, we have created a List of Holidays in the cell range G3 to G17.
Example 01: Calculating the Number of Workdays Between Two Dates Only
In the first example, we will calculate the number of workdays between only two dates using NETWORKDAYS.INTL function in Excel. Here, the [weekend] and [holidays] arguments are not specified. Since these arguments are optional arguments, the NETWORKDAYS.INTL function will work seamlessly without any of them. As mentioned earlier, the default value for the [weekend] argument (1), and the default value for the [holidays] argument (empty array) will be used in this formula.
Firstly, enter this formula in the first cell of the column Total Workdays.
=NETWORKDAYS.INTL(C5,D5)After that, press ENTER on your keyboard.
Formula Breakdown
- =NETWORKDAYS.INTL(C5,D5) has the start_date C5 which contains 1-Feb-20, and the end_date D5 which contains 20-Nov-20.
- It has no [weekend] argument, which means, the default value for the [weekend] argument (1) will be used. The weekends are Saturday and Sunday.
- It also doesn’t have any [holidays] argument. So, the default value of the [holidays] argument (empty array) will be used here.
- So we get the total number of days from 1-Feb-20 to 20-Nov-20. This is 210.
Then, drag the Fill Handle to copy the formula to the rest of the cells.
| Formula | Output | Explanation |
|---|---|---|
| =NETWORKDAYS.INTL(C5,D5) | 210 | Returns the total number of workdays between the dates in cells C5 and D5. |
| =NETWORKDAYS.INTL(C6,D6) | 195 | Returns the total number of workdays between the dates in cells C6 and D6. |
| =NETWORKDAYS.INTL(C7,D7) | 250 | Returns the total number of workdays between the dates in cells C7 and D7. |
| =NETWORKDAYS.INTL(C8,D8) | 218 | Returns the total number of workdays between the dates in cells C8 and D8. |
| =NETWORKDAYS.INTL(C9,D9) | 206 | Returns the total number of workdays between the dates in cells C8 and D8. |
Read More: How to Use NETWORKDAYS Function in Excel (6 Examples)
Example 02: Computing the Number of Workdays Between Two Dates with Weekend Days
Now, we will use the NETWORKDAYS.INTL function with the [weekend] argument specified but the [holidays] argument kept blank. Therefore, the default value of the [holidays] argument (empty array) will be used in this formula.
Firstly, use the following formula in cell E5 of the Total Wrokdays column.
=NETWORKDAYS.INTL(C5,D5,11)After that, hit the ENTER key on your keyboard.
Formula Breakdown
- =NETWORKDAYS.INTL(C5,D5,11) has the start_date C5 which contains 1-Feb-20, and the end_date D5 which contains 20-Nov-20.
- It has 11 as [weekend] argument, which means, we have considered only Sunday as the holiday.
- It doesn’t have any [holidays] argument. So, the default value of the [holidays] argument (empty array) will be used in this here.
- So we get the total number of days from 1-Feb-20 to 20-Nov-20. This is 252.
Now, you can use the AutoFill option of Excel to get the remaining outputs, as demonstrated in the following picture.
| Formula | Output | Explanation |
|---|---|---|
| =NETWORKDAYS.INTL(C5,D5,11) | 252 | Returns the total number of workdays between the dates in cells C5 and D5 |
| =NETWORKDAYS.INTL(C6,D6,11) | 234 | Returns the total number of workdays between the dates in cells C6 and D6. |
| =NETWORKDAYS.INTL(C7,D7,11) | 301 | Returns the total number of workdays between the dates in cells C7 and D7. |
| =NETWORKDAYS.INTL(C8,D8,11) | 261 | Returns the total number of workdays between the dates in cells C8 and D8. |
| =NETWORKDAYS.INTL(C9,D9,11) | 248 | Returns the total number of workdays between the dates in cells C8 and D8. |
Read More: How to Use DATEDIF Function in Excel (2 Ideal Examples)
Similar Readings
- How to Use SECOND Function in Excel (3 Suitable Examples)
- Use MINUTE Function in Excel (6 Suitable Examples)
- How to Use HOUR Function in Excel (7 Easy Examples)
- Use EDATE Function in Excel (5 Simple Examples)
- How to Use the Excel DAYS Function with a Practical Example
Example 03: Calculating the Number of Workdays Between Two Dates with Weekend Days and Holidays
In this example, we will use both the [weekend] and the [holidays] arguments to calculate the number of workdays between 2 dates.
Firstly, we will use the following formula in cell E5.
=NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$18)Formula Breakdown
- =NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$18) has the start_date C5 which contains 1-Feb-20 and the end_date D5 which contains 20-Nov-20.
- It has the [weekend] argument 1, which means, we have considered Saturday and Sunday as the holidays.
- And we have a list of holidays from cell G5 to G18 as the [holiday] argument.
- So we get the total number of days from 1-Feb-20 to 20-Nov-20 excluding the Saturday and Sunday of each week and the days from the holiday list. This is 210.
- Same for the rest of the cells.
- We used the Absolute Cell Reference for the list of holidays ($G$5:$G$18) but not for the start_date and end_date because we want [holidays] to keep fixed but the start_date and end_date to increase when dragging the Fill Handle.
| Formula | Output | Explanation |
|---|---|---|
| =NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$18) | 210 | Returns the total number of workdays between the dates in cells C5 and D5 |
| =NETWORKDAYS.INTL(C6,D6,1,$G$5:$G$18) | 195 | Returns the total number of workdays between the dates in cells C6 and D6. |
| =NETWORKDAYS.INTL(C7,D7,1,$G$5:$G$18) | 250 | Returns the total number of workdays between the dates in cells C7 and D7. |
| =NETWORKDAYS.INTL(C8,D8,1,$G$5:$G$18) | 218 | Returns the total number of workdays between the dates in cells C8 and D8. |
| =NETWORKDAYS.INTL(C9,D9,1,$G$5:$G$18) | 206 | Returns the total number of workdays between the dates in cells C8 and D8. |
Read More: How to Use WORKDAY Function in Excel (7 Suitable Examples)
Example 04: Using NETWORKDAYS.INTL with IF Function
If you want, you can use NETWORKDAYS.INTL within any other function.
Let’s say that the CEO of Sunshine Group has decided to give a bonus to those employees who worked more than 200 days in 2020.
We can use the NETWORKDAYS.INTL functions within an IF function to determine which employees are to be given a bonus, and which are not.
Firstly, we will create a new column named Decision Column and enter this formula in the first cell of the column:
=IF(NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200,"Give","Do not Give")Formula Breakdown
- NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18) returns the number of workdays between the dates in cell C5 and D5, taking Saturday and Sunday as weekends and the days in cell range H5 to H18 as holidays (Just like the previous section)
- NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200 returns a logical value of either TRUE or FALSE. TRUE if the total number of workdays is greater than 200, FALSE otherwise.
- Finally, IF(NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200,”Give”,”Do not Give”) tells “Give” if the total number of workdays is greater than 200, tells “Do not Give” otherwise.
Then, drag the Fill Handle through the rest of the cells of the column.
| Formula | Output | Explanation |
|---|---|---|
| =IF(NETWORKDAYS.INTL(C5,D5,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C5 and D5 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C6,D6,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Do not Give | Returns “Give” if the total number of workdays between C6 and D6 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C7,D7,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C7 and D7 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C8,D8,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C8 and D8 is greater than 200, returns “Do not Give” otherwise. |
| =IF(NETWORKDAYS.INTL(C9,D9,1,$H$5:$H$18)>200,”Give”,”Do not Give”) | Give | Returns “Give” if the total number of workdays between C9 and D9 is greater than 200, returns “Do not Give” otherwise. |
Common Errors While Using NETWORKDAYS.INTL Function
While working with the NETWORKDAYS.INTL function in Excel, we often encounter some common errors. These errors and the reasons behind their occurrence are discussed in the following table.
| Error | When They Show |
|---|---|
| #VALUE! | Occurs when the [weekend] argument is not valid. For example, if you write =NETWORKDAYS.INTL(C4,D4,20,G4:G17), it will show #VALUE! Error, because the [weekend] argument 20 is not valid. Furthermore, occurs when you enter any value or a cell with a value in place of start_date or end_date that is not a date. |
| #NUM! | Occurs when the start_date or end_date is out of range for the current database. |
NETWORKDAYS vs NETWORK.INTL Function in Excel
In Excel, there is another similar function named the NETWORKDAYS function. The only difference between the NETWORKDAYS function and the NETWORK.INTL function is that there is no [weekend] argument in the NETWORKDAYS function. Let’s use both of the functions and see how much they differ from one another.
Firstly, use the following formula in cell B13.
=NETWORKDAYS.INTL(C5,D5,11,$F$5:$F$18)Here, we used 11 as the [weekend] argument.
Then, press the ENTER key on the keyboard.
As a result, you will have the Total Workdays for Natalia Austin using the NETWORK.INTL function in cell B13 as shown in the image below.
Following that, we will use the NETWORKDAYS function in cell C13. So, let’s apply the following formula in cell C13.
=NETWORKDAYS(C5,D5,$F$5:$F$18)Here, there is no scope to specify the number of weekend days in a week. The NETWORKDAYS function considers Saturday and Sunday as the weekend days by default in all cases.
Then, press ENTER.
Consequently, you will have the Total Workdays of Natalia Austin using the NETWORKDAYS function in cell C13 as shown in the following picture.
Finally, select cells B13 and cell C13 together and drag the Fill Handle to copy down the formula and obtain the remaining outputs as demonstrated below.
Conclusion
So, you can use the NETWORKDAYS.INTL function to calculate the number of workdays easily between any two dates in Excel. If you have any questions, please feel free to inform us in the comment section. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!
Related Articles
- How to Use TODAY Function in Excel (6 Easy Examples)
- Use DAY Function in Excel (3 Ideal Examples)
- How to Use DATE Function in Excel (10 Ideal Examples)
- Use Power Query Date Functions in Excel (3 Examples)
- How to Use EDATE Formula for Days (3 Ideal Examples)
- Use TIMEVALUE Function in Excel (4 Examples)
- How to Use Excel WEEKNUM Function (5 Examples)
