HOW TO FIND OUT WORKING DAYS BETWEEN TWO GIVEN DATES IN GOOGLE SHEETS?

Table of Contents

INTRODUCTION

NETWORKDAYS function comes under the DATE AND TIME FUNCTIONS category in GOOGLE SHEETS.

NETWORKDAYS FUNCTION simply returns number of working days between two dates excluding the weekends 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.

NETWORKINGDAYS function considers the SATURDAY and SUNDAY as holidays. In addition to this, we can enter the specific dates which are holidays between the range of the days given.

PURPOSE OF NETWORKDAYS FUNCTION IN GOOGLE SHEETS

NETWORKDAYS return the number of working days between the two dates with the convenience of inserting holidays(dates) in between.

NETWORKDAYS FUNCTION in GOOGLE SHEETS helps us to find out the working days between two given dates. The default OFF DAYS are saturday and sunday. We can insert other holidays too between the given dates.

These options lets us calculate the net working days between the two dates.

PREREQUISITES TO LEARN NETWORKDAYS

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 GOOGLE SHEETS .
  •  Of course, LOGIN TO GOOGLE ACCOUNT AND INTERNET CONNECTION.

Helpful links for the prerequisites mentioned above What Excel does? How to use formula in Excel?

SYNTAX: NETWORKDAYS FUNCTION IN GOOGLE SHEETS

The Syntax for the function is

NETWORKDAYS( START DATE    ,    END DATE   ,     HOLIDAYS (DATES)   )

START DATE  Starting date is the starting date of the period.

END DATE   END DATE is the ending date of the period.

Holidays   It is an optional input. If given , we can specify the dates of holidays in the period .

It 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 OR IN THE DEFAULT FORMAT.

=DATE(YYYY,MM,DD) OTHERWISE IT’LL RETURN AN ERROR.


EXAMPLE: NETWORKDAYS IN GOOGLE SHEETS

DATA SAMPLE

Let us calculate the net workdays between 1.1.2020 to 27.1.2020

CASE 1:

WITHOUT ANY HOLIDAYS

CASE 2:

WITH HOLIDAYS ON 21.1.2020 AND 22.1.2020

NETWORKDAYS FUNCTION EXAMPLE

STEPS TO USE NETWORKDAYS FUNCTION

We have mentioned a starting date, end date and holidays.

CASE 1:The formula used in case 1 is

=NETWORKDAYS(H7,H9)

In this formula H7 is containing the START DATE and H9 is containing the END DATE. 

The result is 19.

CASE 2:

The formula used in case 2 is

=NETWORKDAYS(H7,H9,H11:H12)

In this formula, in addition to the values as case 1, we have inserted a range from H11 TO H12 which are containing two days of holidays.

 The result is 17.

 

MANUAL CHECK OF THE FUNCTION NETWORKDAYS

LET US CONDUCT A MANUAL CHECK OF THE FUNCTION. The picture shows the count of the working days in Case 1.And two more holidays, the 17 is shown as the number of working daysas the result of CASE 2.  

NETWORKDAYS FUNCTION MANUAL CHECK

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.

GOOGLE SHEETS is converting the date to a number first. Number is the number of days after DEC 31,1899.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *