NETWORKDAYS Function In Excel

The best thing about Excel is that it gives us a wide variety of options to choose from its functions based on our needs. And today, we are going to have a look at a similar function, i.e. NETWORKDAYS. Using this function in excel you can easily calculate the number of weekdays between two dates.

As the name suggests, NETWORKDAYS Function in Excel is used to extract the number of working or business days within two given dates by removing out weekends (Saturday and Sunday) and \ or statutory holidays.

So, let’s quickly jump on how to use this function:

How to Use NETWORKDAYS Function:

Before using the NETWORKDAYS function let’s first understand the syntax of this function.

NETWORKDAYS Syntax

As depicted in above figure, NETWORDAYS functions consists of two mandatory and one optional parameter.

Start Date: It is a mandatory parameter and in it you would need to provide the Start Date in DD/MM/YYYY Format. Else you can use ‘Date’ function as well.

End Date: It is a mandatory parameter and in it you would need to provide the End Date in DD/MM/YYYY Format. Else you can use ‘Date’ function as well.

Holidays: It is an optional parameter and in it you would need to provide the range of holidays or any other dates that you do not want to count.

Now let’s head up to the various examples about using it.

Example 1:

Using the NETWORKDAYS to calculate working days between 2 dates using Start and End Date in DD/MM/YYYY format by excluding weekends:

Follow the below listed steps to calculate working days between two given dates by excluding weekends.

  • Click on the cell in which you want to see the output of the function.
  • Verify that ‘Formatting’ of this cell is of ‘General’ Type. To verify right click the cell and click on ‘Format Cells’ as shown below:

Format Cells

Format Cells to General Type

  • After verifying the category click on ‘OK’ or ‘CANCEL’ Button.
  • Now type ‘NETWORKDAYS’ in the formatted cell as shown below:

NETWORKDAYS FORMAT

  • Now enter the cell details of ‘Start Date’ and ‘End Date’. As shown below:

NETWORKDAYS FORMULA FOR EXAMPLE 01

  • After this you can just click outside the cell or hit ‘ENTER’ button and see the output as 15. Reason behind this is that the function is calculating the days from 14th December 2014 to 04th January 2015 (Highlighted in Black in Calendar). Its excluding only the weekends (14, 20, 21, 27, 28 of December and 03, 04  of January).

NETWORKDAYS FUNCTION REMOVING WEEKENDS ONLY

So, this is how you can calculate business days by just excluding the weekends using Start and End Date in DD/MM/YYYY format.

Example 2:

Using the NETWORKDAYS to calculate working days between 2 days by excluding weekends and holidays using Start and End Date in DD/MM/YYYY format:

Follow below listed steps to calculate Working Days between two given dates by excluding weekends and holidays.

  • Click on the cell in which you want to see the output of the function.
  • Verify that ‘Formatting’ of this cell is of ‘General’ Type. To verify right click the cell and click on ‘Format Cells’ as shown below:

Format Cells Format Cells to General Type

  • After verifying the category click on ‘OK’ or ‘CANCEL’ Button.
  • Now type ‘NETWORKDAYS’ in the formatted cell as shown below:

NETWORKDAYS FORMAT

  • Now enter the cell details of ‘Start Date’, ‘End Date’ and ‘Range of Holidays’. As shown below:

NETWORKDAYS FORMULA WITH START DATE, END DATE and HOLIDAYS

  • After this you can just click outside the cell or hit ‘ENTER’ button and see the output as 13. Reason behind this is that the function is calculating the days from 14th December 2014 to 04th January 2015 (Highlighted in Black in Calendar). Its excluding the weekends (14, 20, 21, 27, 28 of December and 03, 04  of January) and Holidays ( 25th Dec and 1st Jan – Highlighted in Red).

NETWORKDAYS FUNCTION OUTPUT EXAMPLE 02

So, this is how you can calculate business days by excluding the weekends and holidays using Start and End Date in DD/MM/YYYY format.

Example 3:

Using the NETWORKDAYS to calculate working days between Start and End Date as ‘DATE’ function:

Before heading onto to the actual topic let’s first understand the syntax of ‘DATE’ function.
‘Date’ function returns the date in MS Excel Format and its syntax is as follows:

DATE SYNTAXDATE FUNCTION EXAMPLEFollow below listed steps to calculate working days between two given dates:

  • Click on the cell in which you want to see the output of the function.
  • Verify that ‘Formatting’ of this cell is of ‘General’ Type. To verify right click the cell and click on ‘Format Cells’ as shown below:

Format Cells Format Cells to General Type

  • After verifying the category click on ‘OK’ or ‘CANCEL’ Button.
  • Now type ‘NETWORKDAYS’ in the formatted cell as shown below:

NETWORKDAYS FORMAT

  • Now enter the cell details of ‘Start Date’, ‘End Date’ and ‘Range of Holidays’. As shown below:

NETWORKDAYS FORMULA FOR EXAMPLE 01

  • After this you can just click outside the cell or hit ‘ENTER’ button and see the output as 15. Reason behind this is that the function is calculating the days from 14th December 2014 to 04th January 2015 (Highlighted in Black in Calendar). Its excluding only the weekends (14, 20, 21, 27, 28 of December and 03, 04  of January).

NETWORKDAYS FUNCTION REMOVING WEEKENDS ONLY

So, this is how you can calculate working days by excluding the weekends and holidays and Start and End Date using ‘DATE’ function.

Example 4:

Using the NETWORKDAYS to calculate working days between 2 days by excluding weekends and holidays using Start and End Date as DATE function:

Before heading onto to the actual topic let’s first understand the syntax of DATE function.
Date Function returns the Date in MS Excel Format and its syntax is as follows:

DATE SYNTAX DATE FUNCTION EXAMPLEFollow below listed steps to calculate working days between two given dates by excluding holidays.

  • Click on the cell in which you want to see the output of the function.
  • Verify that ‘Formatting’ of this cell is of ‘General’ Type. To verify right click the cell and click on ‘Format Cells’ as shown below:

Format Cells Format Cells to General Type

  • After verifying the category click on ‘OK’ or ‘CANCEL’ Button.
  • Now type ‘NETWORKDAYS’ in the formatted cell as shown below:

NETWORKDAYS FORMAT

  • Now enter the cell details of ‘Start Date’, ‘End Date’ and ‘Range of Holidays’. As shown below:

NETWORKDAYS FORMULA WITH START DATE, END DATE and HOLIDAYS

  • After this you can just click outside the cell or hit ‘ENTER’ button and see the output as 13. Reason behind this is that the function is calculating the days from 14th December 2014 to 04th January 2015 (Highlighted in Black in Calendar). Its excluding the weekends (14, 20, 21, 27, 28 of December and 03, 04  of January) and Holidays ( 25th Dec and 1st Jan – Highlighted in Red).

NETWORKDAYS FUNCTION OUTPUT EXAMPLE 02

So, this is how you can calculate working days by excluding the weekends and holidays and Start and End Date using DATE Function.

That’s was all from my end on the various ways to utilize NETWORKDAYS function.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Comments

  1. I am very happy to read your tricks. I wish you could share more exciting tricks :)

    Thnaks for the posts.

Privacy Policy | Disclaimer | About