INTRODUCTION
NETWORKDAYS.INTL function comes under the DATE AND TIME FUNCTIONS category in Excel.
NETWORKDAYS.INTL FUNCTION simply returns number of working days between two dates excluding the weekends [which can be chosen] and any holiday if specified.
e.g. if we want to calculate the wages etc. we can simply put the starting date, end date and holiday dates between the specified dates. The function will return the number of NET WORKING DAYS.
This function is an extension to the older NETWORKDAYS function of excel. It gives an additional option of choosing the days which would be off as the weekend.[Because not all the countries have Saturday and Sunday off].
PURPOSE OF NETWORKDAYS.INTL FUNCTION IN EXCEL
NETWORKDAYS return the number of working days between the two dates with the convenience of inserting holidays(dates) in between.
PREREQUISITES TO LEARN NETWORKDAYS.INTL
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the financial terms is an advantage for the use of such formulas.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: NETWORKDAYS.INTL
The Syntax for the function is
NETWORKDAYS( START DATE , END DATE , WEEKEND CODE , HOLIDAYS (DATES) )
START DATE Starting date is the date of the period.
END DATE END DATE is the end date of the period.
WEEKEND[OPTIONAL] It is given as a code to specify the days of the week which will be treated as weekend.[ Off from work] and won’t be counted for the calculation of full working days between the starting and ending date. It can also be represented by a string specifying , which days are to be treated as holidays[ If the codes combination doesn’t satisfy us].
The codes are as follows.
| Weekend number | Weekend days |
|---|---|
| 1 or omitted | 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 |
WEEKEND [AS A STRING] If we don’t find the combination we need, in the above mentioned table, we can still make it work according to our requirement. Let us learn how to specify the weekend off using the string.
The String has following specifications.
- The string is 7 character long, each character represents a day of the week.[STARTING WITH MONDAY]
- 1 [one] is used for holiday and 0 [zero] is used for working days.
- A standard weekend of Saturday and Sunday off will be represented by 0000011
Holidays It is an optional input. If given , we can specify the dates of holidays in the period .is an optional range of one or more dates(or the cells or range of cells containing the dates) to exclude from the working calendar and treat as holiday in addition to the weekends.
THE DATES SHOULD BE PUT USING THE DATE FUNCTION
=DATE(YYYY,MM,DD) OTHERWISE IT’LL RETURN AN ERROR.
DIFFERENCE BETWEEN NETWORKDAY AND NETWORKDAYS.INTL FUNCTION IN EXCEL
The only difference between the NETWORKDAYS FUNCTION and NETWORKDAYS.INTL FUNCTION in excel is the presence of one additional argument in the syntax which provides us the option of choosing the custom weekend.
In the different parts of the world, the weekend is observed on different days.
In some countries it is SATURDAY, SUNDAY, whereas in some other countries , it is FRIDAY , SATURDAY and so on.
In few countries it is only SUNDAY.
This option wasn’t available in the original NETWORKDAYS FUNCTION and this problem decapitated it from being very useful if the work-off days doesn’t match with the standards i.e. Saturday and Sunday.
Now, Networkdays.intl removes this inability and provides the great option of choosing the weekend. It can be used more confidently in many situations.You can visit NETWORKDAY FUNCTION HERE.
EXAMPLES:NETWORKDAYS.INTL IN EXCEL
DATA SAMPLE
Let us calculate the net workdays between 1.06.2020 and 30.06.2020 with following days as weekends.
EXAMPLE 1:
SATURDAY AND SUNDAY AS OFF
WITHOUT ANY HOLIDAYS
EXAMPLE 2:
FRIDAY AND SATURDAY AS OFF
WITH HOLIDAYS ON 27.6.2020
EXAMPLE 3: MONDAY AND WEDNESDAY AS OFF
NO HOLIDAYS
EXAMPLE 4: NO WEEKENDS. ALL DAYS WORKING.
STEPS TO USE NETWORKDAYS.INTL FUNCTION
We have mentioned a starting date, end date and holidays.The starting date is 1-06-2020 and 30-06-2020
EXAMPLE 1:
The formula used in example 1 is=NETWORKDAYS.INTL(F7,G7,H7,I7)In this formula F7 is containing the START DATE and G7 is containing the END DATE. H7 contains the code and I7 contains the holidays which is none for this case.The code used for 1, which can be referred to the table mentioned above. 1 corresponds to SATURDAY AND SUNDAY. The result is 22 which can be verified manually too. The result is correct.
EXAMPLE 2:
The formula used in example 2 is=NETWORKDAYS.INTL(F7,G7,H7,I7)In this formula F7 is containing the START DATE and G7 is containing the END DATE. H7 contains the code and I7 contains the holidays which is none for this case.CODE used is 7 which corresponds to FRIDAY and SATURDAY. The holiday is on 27-06-2020 which is also incorporated.
The result is 22 which can be verified manually too. The result is correct.
EXAMPLE 3:
The formula used in example 3 is=NETWORKDAYS.INTL(F7,G7,H7,I7)In this formula F7 is containing the START DATE and G7 is containing the END DATE. H7 contains the code and I7 contains the holidays which is none for this case.The weekend code doesn’t satisfy our requirement so we used the String for mentioning the weekend as 1010000 where 1 refers to MONDAY and WEDNESDAY as OFF.
The result is 21 which can be verified manually too. The result is correct.
EXAMPLE 4:
The formula used in example 4 is=NETWORKDAYS.INTL(F7,G7,H7,I7)In this formula F7 is containing the START DATE and G7 is containing the END DATE. H7 contains the code and I7 contains the holidays which is none for this case. We have used 0 for the code as well as 0 for the holidays.
The result is 30 which can be verified manually too. The result is correct.
CONFUSION CLARIFICATIONS
NUMBER OF DAYS CALCULATION
We should always be having knowledge about the working of a function. Otherwise we may be stuck in a situation where we never know whether the answer is correct or not.
These formulas are doing nothing but playing with the dates.
MS Excel is converting the date to a number first. Number is the number of days after JAN 1 1900.
After converting to number it subtracts to find out the number of days between any two dates.
The same procedure is being followed up in these formulas which comprise of DATES.
Although if we need to do this process just once, there is no need of bothering for formulas. But if we need to use this daily, we are exempted from botheration of doing this calculation daily.
The output is again a date as number. Just change the format of the cell to DATE.