Formula Errors

Excel Formula to Compare the Same Weekday This Year vs Last Year

Elise Williams Sep 28, 2026 869 views

Question details

The user needs a formula to calculate the exact same day of the week from the previous year for accurate year-over-year date comparisons.

How to Compare the Same Weekday This Year and Last Year in Excel

Product: Excel
Device & OS: not provided
Scenario: Building weekly business reports or retail dashboards where comparing equivalent days (e.g., current Monday vs. last year's Monday) is required.
Observed behavior: Standard calendar year comparisons shift the day of the week, necessitating a precise 52-week date adjustment to maintain weekday alignment.

Before you start

Confirm your business reporting rules. Some organizations use a strict 52-week retail calendar, while others may require specific adjustments around leap years.

Solution 1Recommended

Use a 364-Day Subtraction Formula

Subtract exactly 52 weeks (364 days) from your target date to return the exact same weekday from the previous year.

Because a standard week has exactly 7 days, 52 complete weeks equal 364 days (52 x 7). Subtracting 364 from any date guarantees the result will fall on the exact same day of the week.

Select the target cell

Click on the empty cell where you want to display the equivalent weekday from last year.

Enter the formula

Type `=A2-364` (assuming your current date is located in cell A2) and press the Enter key.

Format the result as a date

If the result appears as a general number (like 44000), right-click the cell, select 'Format Cells', choose 'Date', and pick a format that displays the day of the week.

Leap Year Considerations: If your 52-week period crosses February 29 during a leap year, the 364-day formula will still correctly match the day of the week, but the calendar date will shift by two days instead of one.

Smart Data Processing

Calculate Year-over-Year Dates with WPS Spreadsheet

Easily track business metrics by comparing equivalent weekdays across years using WPS Spreadsheet. It fully supports standard Excel date and time functions for seamless and accurate reporting.

1. Open WPS Spreadsheet: Launch WPS Office and open your year-over-year reporting workbook.
2. Input your formula: Select a blank cell next to your current year's date and input `=A2-364`.
3. Apply date formatting: Use the Home tab to change the cell format from 'General' to 'Long Date' to easily verify that the weekday matches.
4. Drag to autofill: Click and drag the fill handle down to apply this weekday comparison formula to your entire dataset instantly.

100% compatible with all Microsoft Excel date formulasFree, lightweight, and fast installationBuilt-in templates for business reporting and YoY comparisons

microsoft office alternative - wps office

Frequently Asked Questions

Why use 364 days instead of 365 days for the formula?

364 days equals exactly 52 weeks (52 x 7 = 364). Subtracting 365 days shifts the weekday by one day (or two in a leap year), which breaks year-over-year weekday comparisons.

How do I display the day of the week in my result cell?

Right-click the result cell, select 'Format Cells', navigate to the 'Custom' category, and type 'dddd' in the type box. This forces the cell to display the full weekday name (e.g., Monday).

Does the EDATE function work for matching weekdays?

No. The EDATE function (e.g., =EDATE(A2, -12)) subtracts exactly one calendar month or year. It matches the calendar date, but will usually result in a completely different day of the week.

Excel & Data Problems