Excel Date Minus 1 Year: Formula and Leap-Year Rules

Formulas & functions · Updated November 18, 2026

To subtract one calendar year from a date in Excel, use =EDATE(A2,-12), assuming the original date is in A2. EDATE moves the date back 12 months and handles a leap-day result by returning the last valid day of the target month.

The simplest formula: EDATE

In Microsoft Excel for Microsoft 365 on Windows, enter the formula in the cell where you want the answer, then press Enter. If A2 contains 15 March 2025, the result is 15 March 2024. EDATE counts months rather than subtracting a fixed number of days, which is why it is usually the clearest choice for “one year earlier.”

With the starting date in A2, the formula is =EDATE(A2,-12). The negative number moves backward; a positive number would move forward. The result is an Excel date value, so you can format it, compare it with other dates, or use it in further calculations. The formula does not change the value in A2; it returns the shifted date in the formula cell.

EDATE is available in current Excel versions, including Microsoft 365. The same formula works in Excel for Mac and Excel for the web; there is no different Mac formula. If your regional settings use semicolons as function argument separators, Excel may display or require semicolons instead of commas.

What “minus one year” means

A calendar year is not always 365 days. Leap years have 366 days, and subtracting a year from a particular calendar date normally means keeping the same month and day in the previous year—not moving back a fixed number of days. For example, 15 March 2025 minus one calendar year is 15 March 2024, even though those dates are 365 days apart.

That distinction matters in schedules, contract dates, anniversaries, age calculations, and reporting periods. A one-year shift based on calendar months preserves the intended month-and-day relationship when it can. A fixed-day shift answers a different question: how far back is a specified number of days?

For example, if a report needs the date exactly 365 days before the date in A2, the calculation is different. With the starting date in A2, use =A2-365. This subtracts 365 days, so the resulting month and day may differ from the original. Choose it only when the requirement specifically says 365 days, rather than one calendar year.

Leap years and February 29

The main special case for subtracting one year is February 29. The previous year may not be a leap year, so it has no February 29. EDATE returns the final valid day of the target month: for a starting date of 29 February 2024, =EDATE(A2,-12) returns 28 February 2023. This is generally the practical interpretation of moving a leap-day date back one calendar year.

The result does not mean Excel has made a mistake or silently switched to a 365-day calculation. It has kept the date in the same month and used the last available day when the original day number does not exist in that month. When the target year is a leap year, February 29 can remain February 29.

A tempting alternative is to reconstruct the date with DATE, subtracting one from the year while keeping the same month and day. With the date in A2, =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2)) returns 1 March 2023 for 29 February 2024. DATE normalizes an invalid day by rolling it into the next month; it does not clamp it to February 28. If the intended rule is “last day of February,” use EDATE or a formula that explicitly caps the day.

For a formula that explicitly caps the day to the end of the matching month, with the starting date in A2, use =DATE(YEAR(A2)-1,MONTH(A2),MIN(DAY(A2),DAY(EOMONTH(DATE(YEAR(A2)-1,MONTH(A2),1),0)))). For 29 February 2024, it returns 28 February 2023. This is more complex than EDATE, but it makes the capping rule visible in the formula.

Examples for dates in cells and today’s date

Here are common versions of the calculation. The cell references can be changed to match the layout of your worksheet.

  • With the original date in A2, enter =EDATE(A2,-12) for the same calendar date one year earlier when that date exists.
  • With the starting date in A2, enter =EDATE(A2,12) to move one calendar year forward.
  • To calculate one year before today and have the answer update automatically, enter =EDATE(TODAY(),-12). TODAY returns the current date according to the computer’s date, and the result updates when Excel recalculates.
  • With the date in A2, enter =A2-365 only if the requested interval is exactly 365 days.

For instance, if A2 contains 31 October 2025, EDATE with -12 returns 31 October 2024. If A2 contains 29 February 2024, it returns 28 February 2023. These examples show why EDATE is a calendar-month operation rather than a subtraction of a constant number of days.

How to enter and display the result

  1. Enter a real Excel date in A2, such as 15 March 2025. A date typed into a cell may be displayed according to your regional date settings.
  2. Select the cell for the result, type =EDATE(A2,-12), and press Enter.
  3. If the result appears as a number, select the result cell and choose Home > Number Format. Pick Short Date or Long Date, or open Format Cells to choose a custom date display.
  4. Copy the formula down a column to calculate a result for each row. Excel adjusts the relative reference from A2 to A3, A4, and so on.

The underlying result is a date serial number, while the number format controls how it looks. A numeric display such as 45600 does not necessarily mean the formula failed; it may simply mean the cell is formatted as General or Number. Applying a date format changes the display, not the stored date value.

Choosing the right method for your task

Use EDATE for most requests to subtract one year from a date. It is concise, readable, and handles February 29 by returning the last day of February when necessary. Use subtraction by 365 only when the rule explicitly defines a period as 365 days. The two methods can return different answers, particularly around leap years.

Some policies define a year in a more specific way. A billing agreement might use the same calendar date, a deadline rule might say the last day of the month, and a scientific calculation might require an exact number of elapsed days. Excel cannot decide which interpretation is appropriate from the words “minus one year” alone. Follow the rule that applies to your data, and document it when other people will use the workbook.

For a year-over-year comparison, you may also need to compare a value with the same reporting date last year. Calculate that date with EDATE, then use it to locate or filter the matching record. If your source data has no entry for that exact date, the date formula still works; what happens next depends on how you want to handle missing records.

Common problems and fixes

  • The result shows a number: Apply a date format to the formula cell. Excel stores dates as numbers, and formatting determines whether the serial number is shown as a date.
  • The formula returns an error: Check that A2 contains a valid Excel date rather than text that only looks like a date. Re-enter the date using a format recognized by your regional settings, or convert the source text into a genuine date value.
  • Blank rows show unwanted results: If A2 may be blank and you want the formula cell to stay blank, use the guarded formula below. With the source date in A2, enter =IF(A2="","",EDATE(A2,-12)). It returns an empty string when A2 is blank and otherwise returns the date one year earlier.
  • The answer is one day different from an expected 365-day result: Check whether the requirement means a calendar year or a fixed number of days. EDATE preserves the calendar relationship; subtracting 365 counts days.
  • February 29 becomes February 28 or March 1: EDATE returns the last valid day of February. A DATE formula that carries the day number forward can instead roll into March, so use the method that matches your intended rule.

If dates were imported from another program, inspect a few source cells before filling the formula down. Text dates can be left-aligned or behave differently in comparisons, although alignment alone is not a definitive test because cell formatting and alignment can be changed. A useful check is to change a copy of the cell’s format to General and see whether Excel displays a serial number.

Frequently Asked Questions

What formula subtracts one year from a date in Excel?

With the date in A2, use =EDATE(A2,-12). It moves the date back 12 months and is the standard choice for a calendar year.

Does Excel subtract 365 or 366 days for one year?

EDATE does not subtract a fixed count of days. It moves back 12 calendar months. Use =A2-365 when you specifically need to subtract 365 days; that is not always the same as the same date in the previous year.

What happens when I subtract a year from February 29?

With EDATE, 29 February 2024 minus 12 months becomes 28 February 2023 because February 2023 has no 29th day. A DATE formula that keeps the day number can roll the result into March instead.

How do I subtract one year from today in Excel?

Enter =EDATE(TODAY(),-12). It calculates a date 12 calendar months before today, and the result changes as the current date changes.

Why is my result displayed as a number?

Excel stores dates as serial numbers. Select the result cell and apply a date format from Home > Number Format to display it as a calendar date.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.