How to Use NETWORKDAYS Function in Google Sheets

The NETWORKDAYS function in Google Sheets is useful if you want to know how many working days there are between two dates. 

The function returns the number of days between two given dates, excluding weekends and holidays listed within the Google Sheets. 

Let’s take an example:

You have assignments due for each subject you take in school. You would like to know how many days are available for you to complete the assignments. 

Simply list down all the assignments in Google Sheet with the start and end dates. Then use the NETWORKDAYS function to check the days available. Here is how it looks like on Google Sheets:

NETWORKDAYS Function in Google Sheets

Now you can plan a timetable to work on the weekdays and chill out on the weekends! 🙌✨

 

 

The Anatomy of the NETWORKDAYS Function

The way we write the NETWORKDAYS function is: 

=NETWORKDAYS(start_date,end_date,[holidays])

Let us help you understand the context of the function:

  • The equal sign =  is how we start any function in Google Sheets.
  • NETWORKDAYS() is our function. We need to add two attributes, namely the start_date and end_date, to make it work correctly. We can also add the [holidays] into the formula to exclude those dates. 
  • The start_date is the start date of the period from which to calculate the number of net working days.
  • The end_date is the end date of the period from which to calculate the number of net working days.
  • The [holidays] is a range of dates containing the dates that are considered holidays. This is optional.

Let’s take note that:

The dates used in this formula need to be serial dates, not text dates. If not, the function could not understand the attributes being input.

For example: 

NETWORKDAYS Function in Google Sheets

 

 

A Real Life Example of Using NETWORKDAYS Function

Example 1:

Let’s use a real-life situation to utilize the NETWORKDAYS function to see how the function is used in Google Sheets.

NETWORKDAYS Function in Google Sheets

This example shows how you can create a timeline for actions/ tasks to complete for a particular project or campaign. By using the NETWORKDAYS function, you can exclude non-working days like the weekends and holidays to accurately depict your timeline. 👌

 

 

How to Use NETWORKDAYS Function in Google Sheets

  1. Simply click on the cell that you want to write down your function at. In this example, it will be D2.

Select Index Cell

 

  1. Begin your function with an equal sign =, followed by the name of the function, NETWORKDAYS.  Don’t forget to add an open parenthesis (

NETWORKDAYS Function in Google Sheets

 

  1. We will then select cell B2, as this is the start_date of the task. Furthermore, we need to add a comma , to separate the start_date from our next attribute, the end_date.

NETWORKDAYS Function in Google Sheets

 

  1. Next, we will select cell C2, as this is our end_date.

NETWORKDAYS Function in Google Sheets

 

  1. Then, we will add another comma , to separate the end_date from the [holidays]. To include the [holidays], simply select the range of dates listed in the Google Sheets. We end the formula by closing it with a parenthesis ).

NETWORKDAYS Function in Google Sheets

 

Our final formula would look like this:

=NETWORKDAYS(B2,C2,G2:G6)

 

  1. After the following steps, your input should look like this.

Final Output Results Table

 

 

Example 2:

Let us show you another scenario where you can utilize the NETWORKDAYS function in other areas of your business.

NETWORKDAYS Function in Google Sheets

Payroll can get pretty hectic at the end of each month if you have many workers 💆. By using the NETWORKDAYS function, you can calculate the number of working days for each freelancer excluding the weekends and holidays within the month. 

Apply the same steps from above and you will get the number of working days.

Number of Working Days

After you get the number of working days, multiply it with the rate per day charged. Now you get the total pay for each freelancer and avoid paying more than they required! As simple as that!

 

A Step-up From NETWORKDAYS Function

However, let’s say your company’s “weekends” do not fall on the traditional “Saturday” or “Sunday”. If that’s the case, you can still use the NETWORKDAYS.INTL function. 

The NETWORKDAYS.INTL function is a step up from the NETWORKDAYS function. By including the “INTL” as an additional attribute, it represents which days of the week are considered weekends to you!

 

The formula will look like this:

=NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

Similar to the [holidays] attribute, [weekend] is optional as well.

 

Let’s take note that:

There are two methods to input [weekend] into the formula:

  1. String Method: weekends can be specified using seven 0’s and 1’s, where the first number in the set represents Monday and the last number is for Sunday. A 0 means that the day is a weekday and a 1 means that the day is a weekend. For example, 0000011 would mean Saturday and Sunday are weekends. 
  2. Number Method: instead of using the string method, a single number can be used. Here are the number representations:

Number Method

Let’s take an example:

We shall use Example 2 as an example, but instead of the weekends being Saturday and Sunday, the Company’s policy shows that the weekends are on Fridays and Saturdays.

Easy! We will use NETWORKDAYS.INTL function to modify the Fridays and Saturdays to represent weekends. 

As shown in the table above, to represent Fridays and Saturdays as the weekend, we will use the number 7.

This is how the formula would look like for NETWORKDAYS.INTL function using the number method in Google Sheets:

NETWORKDAYS Function in Google Sheets

 

We can also use the string method. For example, if the company offers Thursdays, Fridays, and Saturdays as weekends, the number method can no longer be applied. No worries! Let’s use the string method in the formula to calculate the working days.

To make Thursdays, Fridays, and Saturdays to represent weekends, the string method would be 0001110. To avoid confusion, here is a table to make things easier:

Number of Days in a week

This is how the formula would look like for NETWORKDAYS.INTL function using the string method in Google Sheets:

NETWORKDAYS Function in Google Sheets

 

You may make a copy of the spreadsheet using the link attached below and try it for yourself:

There you go! 

There are many ways to calculate the days between two given dates, and for different scenarios, a different function may be more suitable. Click here to learn more!

0 Shares:
Leave a Reply

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

You May Also Like