Same Date Last Year (and Period Comparisons)

Excel Formulas › Date & Time

All versionsEDATE

Compare this period to the same one a year ago. EDATE shifts a date by whole months — -12 for one year back — handling month lengths and leap years cleanly.

Quick formula: the same date one year earlier than A2:
=EDATE(A2, -12)
EDATE moves by months and keeps the day where possible, so it’s safer than subtracting 365 days.

Functions used (tap for the full reference guide):

EDATE

The example

One year before June 17, 2026.

AB
1This yearLast year
26/17/20266/17/2025

The formula

Shift back twelve months:

=EDATE(A2, -12) // same date, one year earlier

How it works

EDATE shifts by months, not days:

  1. EDATE(date, months) moves the date by a whole number of months — -12 = one year back, -1 = previous month.
  2. It keeps the same day when possible, and clamps to month-end when the target month is shorter (e.g. Jan 31 → Feb 28).
  3. Subtracting 365 days is wrong across leap years; EDATE handles them correctly.
  4. Pair it with SUMIFS to total the same period last year for a YoY comparison.

Year-over-year totals: =SUMIFS(sales, dates, ">="&EDATE(start,-12), dates, "<="&EDATE(end,-12)) sums the matching window a year ago — the backbone of a YoY growth calc.

Try it: interactive demo

Live demo

Pick a date and a month shift.

Result:

Variations

Previous month

One month back:

=EDATE(A2, -1)

YoY total

Same window last year:

=SUMIFS(sales, dt, ">="&EDATE(s,-12), dt, "<="&EDATE(e,-12))

YoY growth %

Compare the two:

=thisYear / lastYear - 1

Pitfalls & errors

Don’t subtract 365. Leap years make a fixed day count drift; EDATE keeps the calendar date correct.

Month-end clamping. Mar 31 minus a month is Feb 28/29 — EDATE clamps, which is usually what you want but worth knowing.

Format as a date. EDATE returns a serial number.

Practice workbook

📊
Download the free Same Date Last Year (and Period Comparisons) practice workbook
An EDATE shifter with previous-month, YoY-total, and growth variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I get the same date last year in Excel?
Use =EDATE(A2, -12). It shifts by twelve months and handles month lengths and leap years correctly — safer than subtracting 365 days.
How do I total the same period last year?
Use SUMIFS with EDATE bounds: =SUMIFS(sales, dates, ">="&EDATE(start,-12), dates, "<="&EDATE(end,-12)).
Why not just subtract 365 days?
Leap years make 365 days drift off the calendar date. EDATE moves by whole months and stays aligned.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Add months to a date · Sum by month · Percent change & % of total

Function references: EDATE