Compare the Same Weekday Across Weeks in Excel (3 Methods)

Comparing daily data can be misleading.

For example, a Monday usually behaves differently from a Sunday, so comparing them directly may lead to the wrong conclusion.

A better approach is to compare the same weekday across different weeks. For example, comparing all Wednesdays in the past few weeks.

In this post, I’ll walk through 3 different methods to compare the same weekday across weeks in Excel. Each method can work well depending on the workflow and reporting needs.

These examples assume you already have a Pivot Table built from your dataset.

If you prefer to watch the full tutorial, you can view it here:

https://www.youtube.com/watch?v=JCQrsl2u988

If you want to follow along, you can download the sample Excel file here:

Compare-Weekday-Across-Weeks (Completed).xlsx

Download XLSX • 252KB

The file includes all 3 methods shown in this post.

Method 1 — Hardcoded Weekday

This method uses fixed date offsets based on yesterday.

When to use this

This works well when:

• your report is sent daily

• the report format is fixed

• the analysis is always based on yesterday’s data

For example, if you prepare a daily report every morning, you may not need the user to change the weekday manually each time.

Step-by-step

1. In your raw data, add a helper column.

2. Use a formula to check whether the date matches yesterday’s weekday in past weeks.

   Formula (Hardcoded Weekday):

=OR(
[@Date]=TODAY()-1,
[@Date]=TODAY()-8,
[@Date]=TODAY()-15,
[@Date]=TODAY()-22,
[@Date]=TODAY()-29)

*Note: [@Date] refers to the date column in the table. Replace it with your own column name if different.

TODAY()-1	Yesterday’s weekday
TODAY()-8	Yesterday’s weekday in last week
TODAY()-15	Yesterday’s weekday in last 2 weeks
TODAY()-22	Yesterday’s weekday in last 3 weeks
TODAY()-29	Yesterday’s weekday in last 4 weeks

3. The helper column will return TRUE for matching dates and FALSE for others.

   

4. Filter the helper column to TRUE in the Pivot Table.

   

This will allow the Pivot Table to show only the selected weekday pattern across past weeks.

Method 2 — Dynamic Weeks

This method still uses yesterday’s weekday, but allows the user to decide how many weeks to compare.

When to use this

This works well when:

• your report is still based on yesterday

• but the comparison window may change

• for example, 3 weeks, 4 weeks, or 6 weeks

Step-by-step

1. Add a cell where the user can enter the number of weeks.

   

2. In the raw data, add a helper column.

3. Use a formula that checks:

   • whether the row date matches yesterday’s weekday

   • whether the date falls within the selected number of weeks

=AND(
WEEKDAY([@Date])=WEEKDAY(TODAY()-1),
[@Date]>=TODAY()-7*$G$2)

*Note: [@Date] refers to the date column in the table. Replace it with your own column name if different.

"7*$G$2": 7 refers to 7 days a week, while $G$2 refers to the number of weeks input by user.

4. The helper column will return TRUE for rows that meet both conditions (similar to Method 1).

5. Filter the helper column to TRUE in the Pivot Table (similar to Method 1).

This method gives more flexibility where the user can choose how many weeks to compare.

Method 3 — Selectable Weekday

This method allows the user to choose:

• which weekday to compare

• how many weeks to include

When to use this

This is useful when:

• the user needs to backdate analysis

• the user wants to check a specific weekday

• the analysis is more exploratory and not always tied to yesterday

For example, if someone wants to check the trend for Fridays over the past 5 weeks, this method makes that possible.

Step-by-step

1. Add a dropdown list for weekday selection and add another cell where the user can enter the number of weeks.

   

2. In the raw data, create a helper column.

3. Use a formula that checks:

   • whether the row date matches the selected weekday

   • whether the date is within the selected number of weeks

=AND(
[@Day]=$G$2,
[@Date]>=TODAY()-7*$G$3)

*Note: [@Date] refers to the date column in the table. Replace it with your own column name if different.

$G$2 refers to the weekday input by user.

"7*$G$3": 7 refers to 7 days a week, while $G$3 refers to the number of weeks input by user.

4. The helper column will return TRUE for rows that meet both conditions. (similar to Method 1)

5. Filter the helper column to TRUE in the Pivot Table (similar to Method 1).

This creates the most customizable setup.

Which Method Should You Use to Compare the Same Weekday Across Weeks in Excel?

The right choice depends on how the file is used.

• If the report is fixed and always based on yesterday, Hardcoded Weekday or Dynamic Weeks can be more convenient.

• If the user needs more flexibility to backdate or explore trends, Selectable Weekday is more suitable.

In other words:

The best solution isn’t the most flexible — it’s the one that fits the workflow.

Closing

Excel often gives us more than one way to solve the same problem.

What matters is not only whether the formula works, but whether the setup makes sense for the way the file will actually be used.

If you prefer, you can watch the tutorial video above, or download the file and explore all 3 methods directly.