How to Use NETWORKDAYS.INTL Function in Excel (2 Examples)

Overview of the NETORKDAYS.INTL Function of Excel

Today I will be showing how you can calculate the total number of workdays between two dates using the NETWORKDAYS.INTL function of Excel.


NETWORKDAYS.INTL Function of Excel (Quick View)

Overview of the NETORKDAYS.INTL Function of Excel


Download Practice Workbook


Excel NETWORKDAYS.INTL Function: Syntax & Arguments 


Summary

Returns the number of whole workdays between two dates with custom weekend parameters.


Syntax

Syntax of NETWORKDAYS.INTL

=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

Arguments

Argument Required or Optional Value
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:

  • The NETWORK.INTL function counts both the start_date and the end_date.
  • 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
01 Saturday, Sunday
02 Sunday, Monday
03 Monday, Tuesday
04 Tuesday, Wednesday
05 Wednesday, Thursday
06 Thursday, Friday
07 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. 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 weekends each week and the holidays in between.


How to Use the NETWORKDAYS.INTL Function in Excel (Few Examples)


Example 1: Calculating the Number of Workdays of Each Employee in an Office

Let’s begin with the data set below. We have the names, starting dates and the ending dates of 5 employees of a company named Sunshine Group.

A Data Set in Excel

Now we want to count the total number of workdays of each employee. To do that, we first have to create a list of all the holidays.

List of Holidays for NETWORKDAYS.INTL

See, we have created a list of holidays in the cell range G3 to G17.

Now we enter this formula in the first cell of the column Total Workdays:

=NETWORKDAYS.INTL(C4,D4,1,$G$4:$G$17)

And then drag the Fill Handle to copy the formula to the rest of the cells.

So the five cells in column E has these five formulas:

  • NETWORKDAYS.INTL(C4,D4,1,$G$4:$G$17)
  • NETWORKDAYS.INTL(C5,D5,1,$G$4:$G$17)
  • NETWORKDAYS.INTL(C6,D6,1,$G$4:$G$17)
  • NETWORKDAYS.INTL(C7,D7,1,$G$4:$G$17)
  • NETWORKDAYS.INTL(C8,D8,1,$G$4:$G$17)

NETWORKINTL Formula in Excel

Explanation of the Formula:

  • NETWORKDAYS.INTL(C4,D4,1,$G$4:$G$17) has the start_date C4 which contains 1-Feb-20 and the end_date D4 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 G4 to G17 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$4:$G$17) 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(C4,D4,1,$G$4:$G$17) 210 Returns the total number of workdays between the dates in cells C4 and D4.
=NETWORKDAYS.INTL(C5,D5,1,$G$4:$G$17) 195 Returns the total number of workdays between the dates in cells C5 and D5.
=NETWORKDAYS.INTL(C6,D6,1,$G$4:$G$17) 250 Returns the total number of workdays between the dates in cells C6 and D6.
=NETWORKDAYS.INTL(C7,D7,1,$G$4:$G$17) 218 Returns the total number of workdays between the dates in cells C7 and D7.
=NETWORKDAYS.INTL(C8,D8,1,$G$4:$G$17) 206 Returns the total number of workdays between the dates in cells C8 and D8.

Read More: How to Use NETWORKDAYS Function in Excel (3 Suitable Examples)


Similar Readings


Example 2: NETWORKDAYS.INTL within Another Function

If you want, you can use NETWORKDAYS.INTL within any other function.

Let’s think that the chief of Sunshine group has decided he would 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.

To do that, we create a new column named Decision Column and enter this formula in the first cell of the column:

=IF(NETWORKDAYS.INTL(C4,D4,1,$H$4:$H$17)>200,"Give","Do not Give")

Then drag the Fill Handle through the rest of the cells of the column.

IF with NETWORKDAYS.INTL Function

Explanation of the Formula

  • NETWORKDAYS.INTL(C4,D4,1,$H$4:$H$17) returns the number of workdays between the dates in cell C4 and D4, taking Saturday and Sunday as weekends and the days in cell range H4 to H7 as holidays (Just like the previous section)
  • NETWORKDAYS.INTL(C4,D4,1,$H$4:$H$17)>200 returns a logical value of either TRUE or FALSE. TRUE if the total number of workdays is greater than 200, FALSE otherwise.

IF Function in Excel

  • Finally, IF(NETWORKDAYS.INTL(C4,D4,1,$H$4:$H$17)>200,”Give”,”Do not Give”) tells “Give” if the total number of workdays is greater than 200, tells “Do not Give” otherwise.
Formula Output Explanation
=IF(NETWORKDAYS.INTL(C4,D4,1,$H$4:$H$17)>200,”Give”,”Do not Give”) Give Returns “Give” if the total number of workdays between C4 and D4 is greater than 200, returns “Do not Give” otherwise.
=IF(NETWORKDAYS.INTL(C5,D5,1,$H$4:$H$17)>200,”Give”,”Do not Give”) Do not 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$4:$H$17)>200,”Give”,”Do not Give”) 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$4:$H$17)>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$4:$H$17)>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.

Related Content: How to Use DATEDIF Function in Excel (2 Examples)


Common Errors While Using NETWORKDAYS.INTL Function

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.
  • 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.

Conclusion

Using these methods, you can use the NETWORKDAYS.INTL function to calculate the number of workdays between any two dates in Excel. Do you have any questions? Feel free to inform us.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo