EXCEL FUNCTIONS-NETWORKDAYS.INTL

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 numberWeekend days
1 or omittedSaturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday 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.

EXCEL: NETWORKDAYS.INTL FUNCTION EXAMPLE

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.

Learn how to change the format of cell.