How to Count the Number of Workdays Between Dates in Google Sheets

To effectively utilize the NETWORKDAYS function, it is essential to internalize its structure, or syntax. Like all spreadsheet functions, it follows a defined input format that dictates how the calculation is processed. The function requires two mandatory arguments and accepts one optional argument, allowing users flexibility in handling holiday exclusions.

The standard syntax for the NETWORKDAYS function in Google Sheets is as follows, where each component plays a crucial role in defining the calculation parameters.

You can use the NETWORKDAYS function in Google Sheets to count the number of workdays between two dates.

This function uses the following basic syntax:

NETWORKDAYS(start_date, end_date, [holidays])

where:

• start_date: This is the mandatory initial date from which the counting begins. This date must be input as a serial date number or a reference to a cell containing a valid date format.
• end_date: This is the mandatory concluding date for the calculation. The function counts up to and including this date. It must also be a valid date format or cell reference.
• holidays: This is the optional argument, typically represented as a range of cells containing a list of specific non-working dates (e.g., A1:A10). Any date listed in this range will be excluded from the final count, regardless of whether it falls on a weekday or weekend.

Proper formatting of the start and end dates is paramount. Google Sheets interprets dates as numerical values (serial numbers), but for user convenience, standard date formats (like MM/DD/YYYY or YYYY-MM-DD) are readily accepted when input into a cell. If the dates are not recognized correctly, the function will likely return an error, usually #VALUE!. Always ensure the cells referenced contain proper date values before execution.

Prerequisites: Ensuring Correct Date Formatting

Before implementing the NETWORKDAYS function, users must confirm that all dates involved—the start date, the end date, and any listed holidays—are correctly formatted within Google Sheets. Spreadsheets rely on a system of serial numbers to internally manage dates, where January 1, 1900, is typically serial number 1. While the sheet displays human-readable dates, the underlying calculation requires this numeric value. If a date is entered merely as text (e.g., typing “Jan 1, 2022” into an unformatted cell), the function will fail.

To verify correct formatting, select the cell containing the date and navigate to the formatting options (Format > Number). If the cell is genuinely recognized as a date, it will display a date format (like 12/27/2022) or the underlying serial number if you select the ‘Number’ category. If the formatting defaults to ‘Plain text’, the input must be corrected. The easiest way to ensure proper formatting is to input the date using a standard format like MM/DD/YYYY and let Google Sheets automatically convert it.

For the optional holidays argument, it is crucial that the referenced range contains only valid dates. Mixed data types (e.g., text descriptions next to dates) within the holiday range will cause the function to malfunction or miscalculate. Best practice dictates creating a separate column or sheet dedicated solely to listing the official non-workdays, ensuring that every cell within this range adheres to the validated date format required by the NETWORKDAYS function. Maintaining this discipline ensures reliable and consistent results.

Example 1: Basic Workday Calculation Without Holiday Exclusion

The most common application of NETWORKDAYS involves finding the total number of business days between two points in time, relying solely on the exclusion of standard weekends. This scenario is ideal for calculating the length of short-term projects or standard work cycles where federal or public holidays are not a factor or are already accounted for elsewhere.

Suppose we want to calculate the number of workdays between January 1, 2022, and January 10, 2022. Assume the start date (1/1/2022) is located in cell A2, and the end date (1/10/2022) is located in cell B2.

Suppose we want to calculate the number of workdays between 1/1/2022 and 1/10/2022.

We can use the following formula to do so:

=NETWORKDAYS(A2, B2)

In this formula, we omit the optional third argument because we are only interested in excluding Saturdays and Sundays. The function processes the 10 days in this range and automatically identifies which ones fall on the weekend. The result will be the total number of weekdays, Monday through Friday, that occurred between and including the specified dates.

The following screenshot shows how to use this formula in practice:

There are 6 workdays between 1/1/2022 and 1/10/2022.

Verification and Interpreting the Results

After executing the basic NETWORKDAYS calculation, it is crucial to verify the outcome, particularly when first learning the function. For our range of 1/1/2022 to 1/10/2022 (ten calendar days), the function returned 6 workdays. To understand why this is the correct result, we must manually check the calendar for January 2022.

January 1st, 2022, was a Saturday (a weekend day). January 2nd was a Sunday (a weekend day). The working days begin on Monday, January 3rd, and proceed through Friday, January 7th (5 workdays). Saturday, January 8th, and Sunday, January 9th are excluded. The calculation ends on Monday, January 10th, which is the 6th workday. The function correctly identified and excluded the four weekend days (Jan 1, 2, 8, 9) within the ten-day period.

We can confirm this by counting the number of workdays between the two dates on a calendar:

There are indeed 6 workdays between the two dates. This verification step confirms that the NETWORKDAYS function accurately handles the standard Saturday and Sunday exclusion rule. Interpreting the result means recognizing that 6 is the number of days personnel would typically be expected to work or business transactions would typically occur during that interval.

Example 2: Counting Workdays While Specifying Holidays

In real-world business environments, simply excluding weekends is often insufficient, as public and organizational holidays also represent non-working days. The full power of the NETWORKDAYS function is realized when the optional third argument—the holiday list—is included. This ensures maximum accuracy for long-term project planning or fiscal reporting, which span multiple months or even years.

For this example, let us consider a longer period, running from March 1, 2022, to December 27, 2022. We also need to exclude several specific holidays that fall within this range. Suppose the start date is in A2, the end date is in B2 (for this new, longer range), and the list of holidays is stored in the range A8:A11.

The list of holidays we intend to exclude is:

• Easter (4/17/2022)
• Halloween (10/31/2022)
• Thanksgiving (11/24/2022)
• Christmas (12/25/2022)

It is important to note that the function handles holidays that fall on weekends intelligently. If a holiday date (e.g., Christmas on 12/25/2022, which was a Sunday) is listed, the function ensures it is not double-counted as a non-working day. However, listing holidays that fall on weekends is generally harmless and helps maintain a complete record of designated non-workdays.

We can use the following formula to do so:

=NETWORKDAYS(A2, B2, A8:A11)

By referencing the range A8:A11, we instruct the NETWORKDAYS function to first calculate the total M-F workdays between the start and end dates, and then subtract any day listed within the holiday range that also falls on a Monday through Friday.

The following screenshot shows how to use this formula in practice:

There are 214 workdays between 3/1/2022 and 12/27/2022, excluding the list of holidays that we specified.

Advanced Considerations and Limitations of NETWORKDAYS

While the NETWORKDAYS function is highly effective for standard business calculations, it is essential to understand its inherent limitations and how to overcome them for more complex scheduling needs. The primary limitation is its rigid definition of a weekend: Saturday and Sunday are always excluded, without exception. This model works perfectly for a five-day work week (Monday to Friday).

However, if your organization operates on a non-standard schedule—for instance, a four-day work week, or if the weekend falls on Friday and Saturday (common in some regions)—the standard NETWORKDAYS function will provide an incorrect result. In such cases, users should employ the more versatile function, NETWORKDAYS.INTL. This international version allows the user to specify which days of the week constitute the weekend using a numerical code (e.g., 1 for Saturday/Sunday, 2 for Sunday/Monday, 7 for Friday/Saturday).

Another consideration involves handling partial days. NETWORKDAYS counts full calendar days. If a project starts halfway through the first day or ends halfway through the last day, the function still treats both the start and end dates as full days, provided they are workdays. For calculations requiring sub-day precision (e.g., counting work hours), this function is inadequate and must be combined with time-based calculations or other advanced formulas involving the HOUR and MINUTE functions. However, for standard workday counts, NETWORKDAYS remains the definitive tool in Google Sheets.

Note: You can find the complete documentation for the NETWORKDAYS function in Google Sheets.