Excel Formula to Count Dates Excluding Weekends

Tracking project timelines in Excel often becomes frustrating when manual weekend calculations lead to reporting errors. While relying on standard date subtraction methods-the basic resource source for most timeline metrics-provides a total day count, it fails to isolate actual working days. Fortunately, the NETWORKDAYS function grants users the power to automatically exclude weekends, ensuring precise resource allocation. As a critical stipulation, however, custom public holidays must be designated in a separate range to be excluded. For example, a project spanning October 1 to October 15 yields exactly 11 business days. Below, we outline the formula syntax and implementation steps.

When managing projects, tracking employee timesheets, calculating SLA response times, or planning delivery schedules, you often need to calculate the number of days between two dates. However, standard subtraction (End_Date - Start_Date) counts every single calendar day, including Saturdays and Sundays. For business operations, this is rarely useful.

To get an accurate count of business days, you must exclude weekends. Excel provides powerful, built-in functions designed specifically for this purpose, as well as customizable array formulas for more complex scenarios. In this comprehensive guide, we will explore the best methods to count dates while excluding weekends, ranging from basic calculations to highly customized tracking systems.

Method 1: The Classic NETWORKDAYS Function

The easiest and most common way to calculate the number of workdays between two dates is by using the NETWORKDAYS function. By default, this function automatically excludes Saturdays and Sundays. It also has an optional parameter to exclude a custom list of holidays.

Syntax of NETWORKDAYS

=NETWORKDAYS(start_date, end_date, [holidays])

• start_date: The starting date of the period you want to measure.
• end_date: The ending date of the period.
• holidays (optional): A range of cells containing dates that should also be excluded from the count (e.g., national holidays, corporate closures).

Example Scenario

Suppose you have a project that starts on October 2, 2023 (stored in cell A2) and ends on October 16, 2023 (stored in cell B2). To find the number of working days between these two dates, enter the following formula:

=NETWORKDAYS(A2, B2)

Excel will calculate the total number of days (15 calendar days) and subtract the two weekends (October 7-8 and October 14-15), returning a result of 11 working days. Note that NETWORKDAYS is inclusive of both the start and end dates if they fall on weekdays.

Excluding Holidays with NETWORKDAYS

If there is a holiday during your project timeframe-for example, Columbus Day on October 9, 2023-you can list that date in another cell (e.g., cell D2) and reference it in your formula:

=NETWORKDAYS(A2, B2, D2)

This will reduce your result to 10 working days.

Method 2: Custom Workweeks with NETWORKDAYS.INTL

Standard weekends (Saturday and Sunday) do not apply to every industry or region. For instance, many businesses in the Middle East observe weekends on Friday and Saturday. Alternatively, retail workers or healthcare professionals might only have Sundays off.

To solve this, Excel introduced the NETWORKDAYS.INTL function, which allows you to define exactly which days of the week should be treated as weekend days.

Syntax of NETWORKDAYS.INTL

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

The [weekend] argument can be defined using a numeric code or a 7-character string of 1s and 0s.

Using Weekend Codes

Below is a table showing the numeric codes you can use to define your custom weekend schedule:

If you operate on a retail schedule where only Sunday is excluded, your formula would look like this:

=NETWORKDAYS.INTL(A2, B2, 11)

Using Binary String Codes

If you have highly unusual schedules, you can use a 7-character string representing the days of the week, starting with Monday. A 1 represents a non-workday (weekend), and a 0 represents a workday.

• "0000011" = Saturday and Sunday are weekends (Standard).
• "1000001" = Monday and Sunday are weekends.
• "0001000" = Thursday only is a weekend.

To count working days where only Thursday is excluded, use:

=NETWORKDAYS.INTL(A2, B2, "0001000")

Method 3: Counting Weekday Occurrences in a List of Dates

Sometimes you aren't looking for the span of time between a start and end date. Instead, you have a vertical list of miscellaneous dates, and you want to count how many of those specific dates fall on a weekday (excluding Saturdays and Sundays).

For this scenario, NETWORKDAYS will not work because it requires a consecutive range. Instead, we combine SUMPRODUCT and WEEKDAY.

The Formula

=SUMPRODUCT(--(WEEKDAY(A2:A20, 2) < 6))

How It Works

1. WEEKDAY(A2:A20, 2): The WEEKDAY function extracts the day of the week from your date range. By using 2 as the second argument, we configure the function to return:
   • 1 for Monday
   • 5 for Friday
   • 6 for Saturday
   • 7 for Sunday
2. < 6: This evaluates whether the weekday number is less than 6 (which corresponds to Monday through Friday). This returns an array of TRUE (for weekdays) and FALSE (for weekends).
3. The Double Unary (--): Excel cannot directly add up logical values (TRUE/FALSE). The double negative sign converts TRUE into 1 and FALSE into 0.
4. SUMPRODUCT: Finally, this function sums the resulting array of 1s and 0s, giving you a precise count of dates that are weekdays.

Method 4: Adding Criteria to Weekday Counts

What if you need to count dates that are weekdays and also meet an additional condition? For example, you want to count how many tasks were completed on a weekday, where Column A has the dates and Column B lists the status as "Completed".

You can expand the SUMPRODUCT formula to incorporate this criteria:

=SUMPRODUCT((WEEKDAY(A2:A20, 2) < 6) * (B2:B20 = "Completed"))

In this dynamic formula, Excel multiplies the two conditions. If a row is both a weekday (TRUE/1) and marked "Completed" (TRUE/1), the math resolves to 1 * 1 = 1. If either condition is false, it resolves to 0. The sum of these operations yields your filtered weekday count.

Troubleshooting & Common Pitfalls

If your formulas are returning errors or incorrect totals, check for these common issues:

• Dates Stored as Text: Excel formulas cannot calculate dates if they are stored as text values. If your cells are left-aligned and do not change formats, they might be plain text. Use the DATEVALUE function or the "Text to Columns" wizard to convert them back into true serial numbers.
• Negative Numbers: If the start_date is chronologically later than the end_date, NETWORKDAYS will return a negative number. If you just want the absolute distance, wrap your formula in the ABS function: =ABS(NETWORKDAYS(A2, B2)).
• Blank Cells: In standard arrays, blank cells can sometimes be interpreted by the WEEKDAY function as 0, which equates to a Saturday in Excel's underlying system. To avoid false calculations with blank cells in your list, add a check to make sure cells are not empty:

  =SUMPRODUCT((WEEKDAY(A2:A20, 2) < 6) * (A2:A20 <> ""))

Summary

Excluding weekends from date calculations is a key requirement for reliable business reporting. Use NETWORKDAYS for simple workweeks, NETWORKDAYS.INTL for custom shift patterns, and SUMPRODUCT with WEEKDAY when analyzing individual dates within lists. Utilizing these workflows guarantees that your project metrics, timelines, and reporting windows reflect realistic business realities.