How to Count Day Of Week Between Dates in Excel

Why This Task Matters in Excel

Counting how many Mondays, Fridays, or any other specific weekday occur between two dates sounds like a niche requirement until you confront the first report that demands it. Payroll departments need to know how many Saturdays fell in a pay period for weekend differentials. Project managers want to forecast how many Mondays remain before a deadline so they can schedule status meetings. Retail chains often evaluate sales performance by comparing the number of weekend versus weekday shopping days in different months. Facility managers planning preventative maintenance might ask Excel to tell them how many Wednesdays exist in the upcoming quarter because that’s the only day equipment can be serviced without interrupting production.

Excel excels at these calculations because its date-time engine stores every calendar date as a serial number. That internal representation lets us perform simple arithmetic (end − start) to generate day counts or apply specialized functions such as WEEKDAY, NETWORKDAYS, or powerful array-enabled tools like SEQUENCE. When you master counting specific weekdays, you simultaneously strengthen your understanding of dynamic arrays, logical tests, and date arithmetic—skills that translate directly into forecasting, scheduling, and KPI dashboards.

Failing to automate weekday counts has real consequences. Manually scanning calendars leads to errors that propagate through payroll, staffing, or project budgets. Under-counting Saturdays could mean underpaying weekend bonuses; over-counting them might blow up labor costs. Solid weekday calculations also connect to other workflows: conditional formatting rolls out weekend shading, Power Query groups transactions by weekdays, and pivot tables slice performance by day categories. In short, learning to count particular days of the week between dates is a small, concrete step that unlocks broader time-based analysis in Excel.

Best Excel Approach

The fastest, most versatile way to count a given weekday between two dates is to combine the new SEQUENCE function (Excel 365, Excel 2021) with TEXT or WEEKDAY inside a SUMPRODUCT (or the shorthand COUNTIF in dynamic arrays). This method:

• Generates all dates between start and end in one spill array
• Transforms each date into its weekday code or name
• Counts only those that match the target weekday

It is flexible (works for any weekday, supports exclusions, easy to audit), and unlike NETWORKDAYS it is not confined to business days alone.

Recommended formula (modern Excel):

=SUMPRODUCT(--(TEXT(SEQUENCE(end_date-start_date+1,,start_date),"ddd")="Mon"))

Parameters

• start_date – first date of the period
• end_date – last date of the period
• \"Mon\" – three-letter abbreviation of the weekday you want to count

Why this beats alternatives:

• Works for any weekday without nested IFs
• Simpler than old array-confirmed formulas
• Dynamic: change either input date and result refreshes instantly
  Use this method when you have Microsoft 365 or Excel 2021 and you want a single, transparent formula.

Alternative for older Excel versions:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(start_date&":"&end_date)))=2))

Here WEEKDAY(...)=2 matches Mondays because WEEKDAY returns 1 = Sunday, 2 = Monday by default. We wrap the logical test in double unary (--) to convert TRUE/FALSE into 1/0 before SUMPRODUCT aggregates.

Parameters and Inputs

Before dropping formulas into your sheet, confirm the following:

• start_date and end_date must be valid Excel dates—serial numbers formatted as dates. Text strings that look like dates but are not recognized will return errors or wrong counts.
• The range created by SEQUENCE or INDIRECT is inclusive: both start_date and end_date are evaluated.
• Weekday identifiers: TEXT uses locale-dependent abbreviations (\"Mon\", \"Tue\"). Verify your regional settings; in some locales Monday may appear as \"Mo\". WEEKDAY uses numbers 1-7; mapping is stable across locales.
• The target weekday comparison is case-insensitive for TEXT but case-sensitive when you compare plain strings. Stick to the same capitalization.
• Holidays list: if you also need to skip certain dates, you can extend SUMPRODUCT with additional NOT(ISNUMBER(MATCH(..., holidays,0))) logic.
• Edge cases: If start_date is later than end_date a #VALUE! error occurs. Prevent this with a simple IF or app swap: `=IF(`start_date>end_date,\"Invalid\",formula).
• Leap years are automatically handled—Excel’s serial system already accounts for February 29.
• Large ranges (thousands of days) calculate instantly in modern Excel but may lag in large legacy spreadsheets with INDIRECT. Consider dynamic arrays or helper columns for performance.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine you need to know how many Mondays fall between 1-Jan-2023 and 31-Jan-2023. Populate cells as follows:

• B3: 2023-01-01
• B4: 2023-01-31
• B5: \"Mon\" (target weekday abbreviation)

In B7 enter:

=SUMPRODUCT(--(TEXT(SEQUENCE(B4-B3+1,,B3),"ddd")=B5))

Press Enter. Excel spills an implicit array of dates [2023-01-01 … 2023-01-31], converts each to its \"ddd\" abbreviation, checks equality against \"Mon\", turns matches into 1s, and SUMPRODUCT returns 5. January 2023 contains five Mondays.

Why this works: SEQUENCE(B4-B3+1,,B3) builds a vertical list sized (end − start + 1); TEXT translates each serial to \"Sun\",\"Mon\"…; the comparison yields a Boolean array; the double unary converts Booleans to numbers; SUMPRODUCT totals them.

Common variations

• Replace \"Mon\" with \"Sat\" to count Saturdays.
• Change the TEXT format to \"dddd\" to match full weekday names (e.g., \"Sunday\").
• If you prefer numbers, swap TEXT for WEEKDAY and compare to 2 for Monday.

Troubleshooting

• If you get the #NAME? error, SEQUENCE is not available—use the legacy method.
• If the result is zero but you expect a positive count, verify the text abbreviation matches your locale and that start_date ≤ end_date.

Example 2: Real-World Application

Scenario: A call center pays a Sunday premium. Management wants to know how many Sundays occur each fortnight in Q1 2024 to estimate budget. The schedule list resides in a table:

Add a calculated column [Sunday_Count] with:

=SUMPRODUCT(--(TEXT(SEQUENCE([@End_Date]-[@Start_Date]+1,,[@Start_Date]),"ddd")="Sun"))

Because the table uses structured references, each row executes its own sequence. Period 1 returns 2, Period 2 returns 2, and so on. Multiply this Sunday_Count by the premium rate to populate budget projections.

Integration points

• Combine with SUMIFS to aggregate total Sundays per month.
• Feed the table into Power Query for automatic refresh when new rows are added.
• Use conditional formatting to highlight periods with more than two Sundays for quick visual alerts.

Performance considerations

• SEQUENCE over 365 days for six rows is trivial; if you extend to thousands of periods covering decades, consider calculating once in a helper sheet and referencing the totals.

Example 3: Advanced Technique

Edge case: Your company works a custom “4-10” schedule—every employee is off on Fridays. Finance wants to know how many working days remain in the fiscal year after excluding official holidays and Fridays. That is, count Mon-Thu weekdays minus holidays.

Assume:

• Start in B3: TODAY()
• End in B4: 2024-09-30 (fiscal year end)
• Holidays list in [H2:H15]

First compute the raw list of business days (Mon-Fri) excluding holidays with NETWORKDAYS.INTL:

=NETWORKDAYS.INTL(B3,B4,"000010",H2:H15)

\"000010\" string marks Friday as weekend (1) and all other positions Sunday-Thursday as working (0). Now subtract the Fridays themselves to isolate Mon-Thu only. Fridays count:

=SUMPRODUCT(--(TEXT(SEQUENCE(B4-B3+1,,B3),"ddd")="Fri"), --(ISNA(MATCH(SEQUENCE(B4-B3+1,,B3),H2:H15,0))))

Explanation

• The first SEQUENCE spills every date.
• The first logical test picks only Fridays.
• The MATCH + ISNA ensures holiday Fridays are not double-counted (they are already excluded).
  Now your “working days” excluding Fridays equals NETWORKDAYS.INTL result minus Friday count. This pattern shows how you can mix built-in business-day logic with custom weekday counts for complex calendars.

Professional tips

• Wrap the entire construct in LET for clarity and speed.
• In very large date spans (multiple years), spill SEQUENCE into a helper range once instead of inside each formula to avoid redundant calculation.
• Present the result in a dashboard card tied to TODAY() so it updates daily.

Tips and Best Practices

1. Store start_date and end_date in dedicated cells—hard-coded literals inside formulas make maintenance painful.
2. Use full weekday names (\"Monday\") if the file will be opened in multiple regions; TEXT always follows local abbreviations which can change.
3. Convert holiday lists into Excel Tables; structured references automatically expand when you add new holidays.
4. For recurring weekday counts across many rows, wrap the logic in a custom LAMBDA so your sheet reads =CountWeekday(A2,B2,\"Wed\").
5. Named ranges such as rngStart and rngEnd prevent accidental range shifts when inserting rows or columns.
6. Document assumptions (e.g., WEEKDAY numbering system) in a hidden comment or cell note to help future users troubleshoot quickly.

Common Mistakes to Avoid

• Mixing text and numeric date inputs: typing “2024/01/15” as text instead of an Excel date stops SEQUENCE and WEEKDAY from working—re-enter dates or wrap TEXT() around them.
• Forgetting that WEEKDAY defaults to Sunday = 1. Many users assume Monday = 1, leading to off-by-one errors. Always specify the return_type argument or use TEXT.
• Using INDIRECT with large ranges: INDIRECT is volatile; every sheet recalculation rebuilds the array and can freeze workbooks. Prefer SEQUENCE for modern Excel or helper columns in legacy workbooks.
• Overlooking inclusive logic: both boundary dates are counted. If you need open intervals, subtract one from the length or adjust start/end.
• Hard-coding “Sat”/“Sun” rather than referencing a cell; when requirements change to count Tuesdays you’ll edit every formula. Parameterize weekday names in separate cells.

Alternative Methods

Below is a quick comparison of the main techniques:

For performance-critical or enterprise environments, Power Query or DAX in Power Pivot may be preferable. However, for 95 percent of operational reports a well-constructed SEQUENCE formula is both fastest and easiest.

FAQ

When should I use this approach?

Use it any time you need an exact count of a particular weekday within a start–end interval—staff scheduling, payroll premiums, marketing analyses comparing weekend footfall, or calculating recurring meeting occurrences.

Can this work across multiple sheets?

Absolutely. Place start_date and end_date on Sheet1, your formula on Sheet2, and reference them with Sheet1!B2, etc. If you need counts for several sheet-level date pairs, wrap the logic in a LAMBDA stored in the Name Manager.

What are the limitations?

Huge date spans with legacy formulas can slow calculation due to INDIRECT volatility. SEQUENCE is limited to 1,048,576 rows (the row limit). If you must span more, split periods or use Power Query.

How do I handle errors?

Guard against start_date later than end_date with IF(start>end,\"Check dates\",formula). Use IFERROR wrapper for text messages instead of #VALUE!. To debug mismatched weekday names, spill the TEXT array in a helper column so you see which labels are produced.

Does this work in older Excel versions?

Yes, but you need to replace SEQUENCE with ROW(INDIRECT()). Example: `=SUMPRODUCT(`--(WEEKDAY(ROW(INDIRECT(A1&\":\"&B1)))=3)) counts Tuesdays. Remember to confirm as a normal formula; SUMPRODUCT handles the array internally.

What about performance with large datasets?

Dynamic arrays recalculate quickly. For thousands of rows each generating their own SEQUENCE, consider:

• Moving holiday checks to a helper column
• Using LET to store the sequence once
• Converting formulas to values when final counts are approved
  Avoid volatile INDIRECT in large models.

Conclusion

Mastering weekday counts may feel like a small win, but it streamlines scheduling, payroll, and forecasting tasks that occur daily in businesses. The modern SEQUENCE-based formula is concise, transparent, and incredibly flexible, while fallback methods keep older workbooks viable. By learning to parameterize weekdays, incorporate holiday lists, and handle edge cases, you sharpen both your date arithmetic and dynamic array skills—capabilities that translate directly into more advanced time-series analysis. Experiment with the examples, wrap the logic in custom functions, and soon you’ll deploy weekday counts across dashboards with confidence.