How to Count Wednesdays Between Two Dates in Excel – Step by Step Guide

Sometimes you need to know how many specific weekdays, such as Wednesdays, occur between two dates. This can be useful for scheduling, payroll, or project planning. Instead of manually counting each Wednesday on a calendar, Excel provides formulas, functions, and even VBA to automate the process.

Key Takeaways

• Use WEEKDAY with SUMPRODUCT to count Wednesdays between two dates.
• Wednesdays correspond to 4 when using WEEKDAY with default settings.
• ROW and INDIRECT generate date sequences inside formulas for flexible counting.
• Power Query or VBA can simplify the task for large datasets or recurring reports.
• Always check start and end dates to ensure they are valid Excel date values.

What does counting Wednesdays mean in Excel

Counting Wednesdays means calculating how many times a Wednesday occurs within a start and end date. For example, if the period is January 1 to January 31, 2024, there are 5 Wednesdays. Excel can do this automatically using date functions, avoiding manual errors and saving time.

Step by step example using the workbook

We have raw data that contains start and end dates for multiple ranges.

Column C calculates Wednesdays using the formula:
=SUMPRODUCT(–(WEEKDAY(SEQUENCE(B2-A2+1,1,A2))=4))

This formula generates all dates between Start_Date (A2) and End_Date (B2), checks which are Wednesdays, and counts them.

How to Count Wednesdays Between Two Dates in Excel

1. Basic formula:

=SUMPRODUCT(--(WEEKDAY(SEQUENCE(B2-A2+1,1,A2))=4))

This counts the number of Wednesdays between two dates.

2. Adjusting for other weekdays:

Change the =4 part of the formula to match another weekday. For example, =2 for Monday, =6 for Friday.

3. Using VBA custom function:

Function CountWednesdays(StartDate As Date, EndDate As Date) As Long
    Dim d As Date, count As Long
    count = 0
    For d = StartDate To EndDate
        If Weekday(d, vbSunday) = vbWednesday Then
            count = count + 1
        End If
    Next d
    CountWednesdays = count
End Function

You can use =CountWednesdays(A2,B2) after adding this macro.

Common mistakes and tips

Text values instead of dates: Ensure cells are real dates, not text. Convert with =DATEVALUE() if needed.

Incorrect weekday numbers: By default, WEEKDAY returns Sunday = 1, Monday = 2, … Wednesday = 4. Always confirm the numbering system.

Inclusive ranges: The formulas include both start and end dates. Adjust logic if you need exclusive ranges.

Regional settings: Different systems may use different default weekday numbering. Use =WEEKDAY(date,2) if you prefer Monday=1 to Sunday=7.

Bonus tips and advanced scenarios

Use Power Query: Load the date range into Power Query, create a custom column with Date.DayOfWeekName, then filter for “Wednesday” and count rows.

Dynamic named ranges: Create dynamic ranges for Start_Date and End_Date to automate the formula across multiple scenarios.

Conditional formatting: Highlight Wednesdays within a calendar by applying a rule: =WEEKDAY(A1)=4.

Use cases

Payroll scheduling. Count how many Wednesdays fall in a pay period to schedule mid-week payments.

Why it matters: Many businesses schedule payroll on specific weekdays, often mid-week. Knowing exactly how many paydays occur within a given period ensures accurate budgeting and timely cash flow management.

How to do it: Enter the pay period start date in one cell and the end date in another. Use the Wednesday-counting formula =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=4)) to determine how many Wednesdays occur within the range.

Practical add-on: Extend the formula across multiple pay periods by placing start and end dates in a table, then apply the formula in a helper column. This allows payroll teams to quickly see how many mid-week runs occur per month or quarter.

Benefit: Ensures employees are paid consistently, avoids errors in payroll frequency, and helps finance teams prepare for the exact number of payroll disbursements within any fiscal period.

Project planning. Identify number of team check-in meetings (every Wednesday) between start and end dates.

Why it matters: Many project teams set recurring meetings on a specific weekday to review progress. Counting how many of those days exist between the project’s start and end dates helps managers schedule agendas and allocate time effectively.

How to do it: With the project kickoff date in A2 and the completion date in B2, use the Wednesday formula to determine the count. Each Wednesday represents one scheduled check-in. For projects that span months, this gives an accurate tally of required sessions.

Practical add-on: Create a calendar view by listing all dates within the project and applying =TEXT(A2,"dddd") to label weekdays. Filter for Wednesday to generate an actual meeting list with exact dates, not just the count.

Benefit: Ensures that meeting schedules are transparent, avoids conflicts, and gives the project team a clear roadmap of touchpoints during the project lifecycle.

Education. Count how many Wednesday classes occur in a semester for lesson planning.

Why it matters: Schools and universities often run courses on fixed weekdays. Knowing exactly how many sessions fall on a particular weekday is crucial for planning syllabi, exams, and class activities.

How to do it: Input the semester start and end dates into Excel. Apply the formula to count Wednesdays. If classes only run during term time (excluding holidays), remove those dates or adjust the formula by excluding ranges with WORKDAY or by filtering them out in Power Query.

Practical add-on: Build a dynamic schedule by generating all Wednesday dates using =FILTER(SEQUENCE(B2-A2+1,1,A2,1),TEXT(SEQUENCE(B2-A2+1,1,A2,1),"dddd")="Wednesday") (Excel 365). This provides a full list of actual class dates for lesson planning.

Benefit: Teachers can ensure the syllabus fits within the available sessions, prevents overloading certain weeks, and helps administrators align resources such as classrooms and faculty schedules.

FAQ

What weekday number is Wednesday in Excel?
With default settings, Wednesday = 4 when using WEEKDAY(). If using WEEKDAY(date,2), Wednesday = 3.

Can I count other weekdays like Fridays?
Yes, change the formula to =5 for Thursday, =6 for Friday, etc., depending on your system settings.

Does the formula include both start and end dates?
Yes, the ROW(INDIRECT()) method includes both endpoints.

Is VBA faster for large datasets?
Yes, VBA is generally more efficient than ROW(INDIRECT()) for long date ranges.

Can I automate this for recurring reports?
Yes, use VBA for custom functions or Power Query for refreshable reports.