What Is NETWORKDAYS Function In Google Sheets?
The NETWORKDAYS in Google Sheets calculates the total working days between any two given dates, excluding the weekends and specified holidays.
The NETWORKDAYS Google Sheets function mostly find its use while calculating employee benefits, such as bonus, incentives, settlements, vacations, etc.
For example, the below table contains a first and the last date of a month with one listed holiday list. We will count the working dates, using the Google Sheets NETWORKDAYS function excluding weekends and the listed holiday.
Select cell D2, enter the formula =NETWORKDAYS(A2,B2,C2) and press “Enter”, as shown below.
The output is shown above as 20. The actual working days are 21, however, removing one holiday gives this output.
Key Takeaways
- The NETWORKDAYS in Google Sheets calculates the total workdays between two given dates, excluding weekends and specified holidays.
- All the arguments whether mandatory ones or the optional one, must be a valid date value or cell referenced to valid date format, to avoid errors or incorrect results.
- If we want to exclude the specified holidays from the workday count that fall on weekends, then the NETWORKDAYS() counts them as weekends, thus excluding only one instance of such dates from the workday count calculation.
- We can use the NETWORKDAYS() with other Google Sheets functions such as EOMONTH, DATE(), NOW() and IF().
- If the start_date falls after the end_date, i.e., if the end_date precedes the start_date, then thefunctionreturns a negative number.
Syntax
The syntax of the NETWORKDAYS formula in Google Sheets is,
The arguments of the NETWORKDAYS formula in Google Sheets are,
- start_date: A date representing the initial date. It is a mandatory argument.
- end_date: A date representing the end date. It is a mandatory argument.
- [holidays]: One or more holidays to exclude while calculating the work days. It is an optional argument.
How To Use NETWORKDAYS Function In Google Sheets?
We can use the NETWORKDAYS in Google Sheets in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method 1 – Access From the Google Sheets Ribbon →
Step 1: Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Date” option right arrow → select the “NETWORKDAYS” function, as shown below.
Step 2: The “NETWORKDAYS” formulaappears, as shown below. Enter the argument as cell reference.
Method 2 – Enter the Formula in the Worksheet Manually →
Step 1: Select an empty cell for the output.
Step 2: Type =NETWORKDAYS( in the cell, as shown below. [Alternatively, type =N or =NET and double-click the NETWORKDAYS from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some NETWORKDAYS in Google Sheets examples and retrieve working days with and without holidays and with the date and time values as well.
Example #1 – Count Workdays with No Extra Holidays.
Consider the dataset that consists of the dates of the first date of every month as the start date and the corresponding last date of the particular months, of the year 2024. We will count workdays with no extra holidays using the NETWORKDAYS in Google Sheets.
The steps to fetch the workdays without holidays using NETWORKDAYS are,
Step 1: Select cell B2 and enter the formula =NETWORKDAYS(A2,B2), as shown below.
Step 2: Press “Enter” and drag the formula from cell C2 to C13 using the fill handle, to get the following results.
Example #2 – Remove a Holiday from the Workday Count.
Let us consider the result dataset of Example #1 and remove a holiday from the workday count and see the difference. Column C is the result of example 1, i.e. workdays without holidays, column D is a list of random dates, one per month, to be removed from the workday count.
The steps to fetch the workdays with one holiday using NETWORKDAYS are,
Step 1: Select cell E2, enter the formula =NETWORKDAYS(A2,B2,D2) and press “Enter”, as shown below.
Step 2: As seen in the image above, the moment we press “Enter”, the Google Sheets gives an autofill option for the results. We can either choose the autofill option, or use the fill handle method.
Therefore, let us here drag the formula from cell E2 to E13 using the fill handle, to get the following results.
We see the output above, where column C and E have a difference of one value, indicating that we have removed a day as a holiday, from the workday count.
Example #3 – Using Days and Times.
The dataset given below has the dates of the months with 31 days, where the start date is the 15th and the end date is the 31st of each of those months. We have a special scenario here that the dates are added along with the time. Let us find the total workdays using days and times.
The steps to fetch the workdays using the days and time are,
Step 1: Select cell C2, enter the formula =NETWORKDAYS(A2,B2) and press “Enter”, as shown below.
Step 2: Drag the formula from cell C2 to C8 using the fill handle, to get the following results.
Example #4 – Using EOMONTH.
We have some date values in the dataset given below. Instead of having corresponding set of dates, let us use the EOMONTH() function with NETWORKDAYS to fetch the workdays.
The steps to fetch the workdays using EOMONTH are,
Step 1: Select cell B2, enter the formula =NETWORKDAYS(A2,EOMONTH(A2,0)) and press “Enter”, as shown below.
Step 2: Drag the formula from cell B2 to B4 using the fill handle, to get the following results.
Important Things To Note
- Ensure the date values we provide to the NETWORKDAYS Google Sheets function have the data format set as Date.
- When the start_date and end_date arguments have the same date value, the NETWORKDAYS() return value is 1, as the function counts the start and end dates.
- We get the #VALUE! error for invalid argument values.
- If the result cells format is not valid, then we get wrong date value such as 1900. In such cases change the date format using the path, “Format à Number à Number”, as shown below.
- Ensure to provide the optional argument as a date value, because if we give a number then we will get an error.
Frequently Asked Questions (FAQs)
A few reasons the NETWORKDAYS Google Sheets Function may not work are,
One or all of the argument values are not provided or incorrectly entered.
The cell range selected is modified or deleted.
We have inserted the result cells below the dataset and new data is added in the dataset.
The cell value for the arguments given directly are inserted without double-quotes.
There is combination of values, such as numeric values and dates in the selected dataset.
When we combine NETWORKDAYS function with other functions, if the result of the formula, that must serve as an argument is not a date value, then we will get an error.
The format of the result cells is different from the selected cell range data format.
We often forget in which category a function falls, here, the “NETWORKDAYS” function. Then, we can insert the function as follows:
Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “NETWORKDAYS” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the NETWORKDAYS in Google Sheets by following the path shown below.
• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option → click the “All” option right arrow → select the “NETWORKDAYS” function, as shown below.
We can insert the Google Sheets EOMONTH function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Date” option right arrow → select the “EOMONTH” function, as shown below.
We can use the same path to insert other date function as well, such as DATE, DATEVALUE, DAY,DAYS, DAYS360, EDATE, NETWORKDAYS, NETWORKDAYS.INTL, etc, as it is just above and below the EOMONTH function, as seen in the above image. It is useful when we want to combine the date format functions along with the NETWORKDAYS function.
Download Template
This article must help understand the NETWORKDAYS in Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What Is NETWORKDAYS in Google Sheets. We learn its syntax & how to use it to calculate the total working days between any two dates. You can learn more from the following articles. –
Array Formula In Google Sheets
Leave a Reply