In our previous blog, we had learned about the NETWORKDAYS function in excel. In this tutorial, we would learn an extended formula called NETWORKDAYS.INTL function in Excel. As you read this blog, you would understand the following-
- Purpose and objective of NETWORKDAYS.INTL formula in excel
- Difference between NETWORKDAYS and NETWORKDAYS.INTL
- Formula syntax and arguments
- Examples of NETWORKDAYS.INTL formula in excel
- Bonus Section : ExcelUnlocked Trick
Here we go 😎
Objective of NETWORKDAYS.INTL
The =NETWORKDAYS.INTL formula works in a way similar to =NETWORKDAYS formula in excel. It counts the number of working days between two dates in excel excluding weekends and holidays.
NETWORKDAYS vs NETWORKDAYS.INTL in Excel
The major difference between these two formulas is that the NETWORKDAYS does not allow to change the weekends. This means that NETWORKDAYS only considers Saturdays and Sundays as weekend days.
However, the using the NETWORKDAYS.INTL formula one can specify or change the weekend days to something else.
For example, instead of Saturdays and Sundays, you can specify Thursday and Friday as weekend days or even only Sundays.
Syntax and Arguments
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Unlike the NETWORKDAYS formula, the NETWORKDAYS.INTL function has four arguments, as stated below:
- start_date – In this argument, specify the date value or a serial number of the start date.
- end_date – In this argument, enter the date value or a serial number of the end date.
- [weekend] – Weekend is an optional argument. If you want to specify your own custom weekend days, then use this argument, otherwise, leave it blank. If you leave this argument blank, then excel would consider Saturdays and Sundays by default.
- [holidays] – This is also an optional argument. In here, specify the range of cells containing the list of holiday dates that you wish to exclude for days calculation, other than weekends. For example, any national or public holiday.
Example of NETWORKDAY.INTL Function in Excel
Suppose you want to count the number of days between the given dates – 14/07/2020 and 31/12/2020, considering weekend days as Thursdays and Fridays and a list of holidays.
In that case, when you reach to the third argument of the formula (viz, weekends), a drop down list would pop out from which you can select your own custom weekend. See the image below for better clarity.
Finally, in the fourth argument, select the range of cells containing the list of holidays (C2:C4) and press Enter.
As a result, excel would return the output as 122, which is the number of working days between the two days excluding Thursdays, Fridays, and holidays.
Do Not Miss These Points
- The third argument of the function (i.e. weekends) can be either a single day (like, Friday) or any two consecutive days (like, Tuesday and Wednesday).
- The first two arguments (i.e. start date and end date) can be either given as cell reference, or can be manually entered as text. If you choose the second way, then make sure to put the dates within double-quotes. See the example below:
Bonus Section – ExcelUnlocked Tips and Tricks 😉
The NETWORKDAYS.INTL excel function also helps you in counting the number of days between two specified dates using your own custom working and non-working days (for part-time jobs).
For example, suppose your company only works on Tuesdays, Thursdays, and Saturdays. Therefore, Mondays, Wednesdays, Fridays, and Sundays are non-working days.
In such a case, use the combination of integers 0 and 1 in the weekend argument, like this “1010101” (within double-quotes).
In “1010101”, 0 represents working day and 1 denotes non-working day.
Note that the first number, in such 0 and 1 formats, always represent “Monday” followed by Tuesday, and so on.
With this, we have completed with this tutorial on using NETWORKDAYS.INTL formula in Excel.