Count Days Between Dates in Google Spreadsheets

01
of 01

Google Spreadsheets NETWORKDAYS Function

Google Spreadsheets NETWORKDAYS Function
Google Spreadsheets NETWORKDAYS Function. © Ted French

NETWORKDAYS Function Overview

Google Spreadsheets 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;
  • If weekend days are other than Saturday and Sunday - or just one day per week - use the NETWORKDAYS.INTL function.

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;

The NETWORKDAYS Function's Syntax and Arguments

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)

start_date - (required) the start date of the chosen time period

end_date - (required) the end date of the chosen time period

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 - row five in the image above;
  • serial numbers entered as:
    • a range of cell references - row six above;
    • an array constant - row seven above.

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 eight above;
  • The #VALUE! error value is returned if any argument contains an invalid date.

Example: Count the Number of Days between Two Dates

As shown in the image above, several variations of NETWORKDAYS are used to calculate the number of workdays available between July 11, 2016 and November 4, 2016.

Two holidays (September 5 and October 10) that occur during this period are deducted from the total.

The examples show 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 Spreadsheets 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.

The steps below were used to enter NETWORKDAYS and its arguments into cell C5 of the example in the image above.

  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;
  11. The number of working days - 83 - should appear in cell C5 of the worksheet;
  12. When you click on cell C5 the complete function
    =NETWORKDAYS(A3,A4,A5:A6) appears in the formula bar above the worksheet;

The Math behind the Function

How Google Spreadsheets arrives at the answer of 83 in row five is :

  • the total number of weekdays between July 11 and November 4, 2016 equals 85 (17 weeks x 5 days per week) - the result for rows three and four 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.
Format
mla apa chicago
Your Citation
French, Ted. "Count Days Between Dates in Google Spreadsheets." ThoughtCo, Dec. 6, 2016, thoughtco.com/count-the-number-of-days-between-two-dates-3123876. French, Ted. (2016, December 6). Count Days Between Dates in Google Spreadsheets. Retrieved from https://www.thoughtco.com/count-the-number-of-days-between-two-dates-3123876 French, Ted. "Count Days Between Dates in Google Spreadsheets." ThoughtCo. https://www.thoughtco.com/count-the-number-of-days-between-two-dates-3123876 (accessed January 23, 2018).