How to Use Excel NETWORKDAYS.INTL Function (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel NETWORKDAYS.INTL Function

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.


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.


How to Use the NETWORKDAYS.INTL Function in Excel: 4 Practical Examples

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.

Dataset for using NETWORKDAYS.INTL Function in Excel

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.

Calculating the Number of Workdays Between 2 Dates Only using NETWORKDAYS.INTL Function in Excel

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.

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.

Computing the Number of Workdays Between 2 Dates with Weekend Days using NETWORKDAYS.INTL Function in Excel

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.

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.

Calculating the Number of Workdays Between 2 Dates with Weekend Days and Holidays using NETWORKDAYS.INTL Function in Excel

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.

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.

NETWORKDAYS vs NETWORK.INTL Function in Excel

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.


Download Practice Workbook


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.  Happy learning!


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo