Count Days Between Dates in Google Sheets

Tutorial: How to Use the NETWORKDAYS Function

Google Sheets has a number of date functions available, and each function in the group does a different job.

The NETWORKDAYS function can be used to calculate the number of whole business or working days between specified start and end dates. With this function, weekend days (Saturday and Sunday) are automatically removed from the total. Specific days, such as statutory holidays, can be omitted as well.

Use NETWORKDAYS when planning or writing proposals to determine the time frame for an upcoming project or to back-calculate the amount of time spent on a completed one.

01
of 03

NETWORKDAYS Function Syntax and Arguments

Google Sheets NETWORKDAYS Function
© Ted French

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the NETWORKDAYS function is:

=NETWORKDAYS(start_date,end_date,holidays)

The arguments are:

  • start_date – (required) the start date of the chosen period
  • end_date – (required) the end date of the chosen period

Use date values, serial numbers, or the cell reference to the location of this data in the worksheet for both arguments.

  • holidays – (optional) one or more additional dates that are excluded from the total number of working days

Holiday dates can be date values entered directly into the formula or the cell references to the location of the data in the worksheet.

Notes: Since NETWORKDAYS does not automatically convert data to date formats, date values entered directly into the function for all three arguments should be entered using the DATE or DATEVALUE functions to avoid calculation errors, as shown in row 8 of the image that accompanies this article.

The #VALUE! error value is returned if any argument contains an invalid date.

02
of 03

Tutorial: Count the Number of Work Days Between Two Dates

This tutorial illustrates how several variations of the NETWORKDAYS function are used to calculate the number of work days between July 11, 2016, and November 4, 2016, in Google Sheets. 

Use the image that accompanies this article to follow along with this tutorial.

In the example, two holidays (September 5 and October 10) occur during this period and are deducted from the total.

The image shows how the function's arguments can be entered directly into the function as date values or as serial numbers or as cell references to the location of the data in the worksheet.

Steps to Entering the NETWORKDAYS Function

Google Sheets does not use dialog boxes to enter a function's arguments as can be found in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

  1. Click on cell C5 to make it the active cell.
  2. Type the equal sign ( = ) followed by the name of the function networkdays.
  3. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter N.
  4. When the name networkdays appears in the box, click on the name with the mouse pointer to enter the function name and open parenthesis or round bracket " ( " into cell C5.
  5. Click on cell A3 in the worksheet to enter this cell reference as the start_date argument.
  6. After the cell reference, type a comma to act as a separator between the arguments.
  7. Click on cell A4 to enter this cell reference as the end_date argument.
  8. After the cell reference, type a second comma.
  9. Highlight cells A5 and A6 in the worksheet to enter this range of cell references as the holiday argument.
  10. Press the Enter key on the keyboard to add a closing parenthesis " ) " and to complete the function.

The number of working days—83—appears in cell C5 of the worksheet.

When you click on cell C5, the complete function
=NETWORKDAYS(A3,A4,A5:A6) appears in the formula bar above the worksheet.

03
of 03

The Math Behind the Function

How Google Sheets arrives at the answer of 83 in row 5 is :

  • The total number of weekdays between July 11 and November 4, 2016, equals 85 (17 weeks x 5 days per week). It is the result for rows 3 and 4 where the holiday argument is omitted.
  • From this total, the two holiday dates specified (September 5 and October 10) are subtracted to leave a result of 83 working days.

Note: If weekend days are other than Saturday and Sunday or just one day per week, use the NETWORKDAYS.INTL function.

Was this page helpful?