PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

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

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.

 

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.

How to use Networkdays.intl in excel
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.

 

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: