Excel Date Minus Months: Formulas and Examples

Formulas & functions · Updated November 18, 2026

To subtract calendar months from a date in Excel, use EDATE with a negative month count. If the starting date is in A2 and you want to go back three months, enter =EDATE(A2,-3); Excel returns the corresponding date, adjusting to the last valid day when the earlier month is shorter.

The EDATE Function for Subtracting Months

EDATE is the standard choice when you mean calendar months rather than a fixed number of days. Its first argument is the starting date, and its second is the number of months to move. A positive number moves forward; a negative number moves backward. The result is an Excel date value, so it can be used in calculations, comparisons, and other date formulas.

For example, with a date in A2, enter =EDATE(A2,-1) to go back one month. If A2 contains May 15, 2025, the result is April 15, 2025. To move back six months, use =EDATE(A2,-6). If A2 is July 10, 2025, the result is January 10, 2025. The formula works for different starting dates without requiring you to look up how many days each month has.

The month count can also come from a cell. This is useful when you want to change the interval without editing the formula. If A2 contains the date and B2 contains a positive number of months to subtract, enter =EDATE(A2,-B2). With A2 set to July 10, 2025 and B2 set to 6, the result is January 10, 2025.

How Excel Handles Calendar Dates

Excel stores dates as serial numbers and displays them using a date format. EDATE calculates from the date value, not from the way the date looks on screen. A cell that displays as 15-May-2025 and one that displays as May 15, 2025 can represent the same date value; the display format does not change the month calculation.

Calendar months have different lengths, so there is not always a matching day in the destination month. If the starting date is the 31st and the previous month has only 30 days, the result is the 30th. If the destination month has 28 days, the result is the 28th, or the 29th in a leap year. Excel does not invent an extra date or carry the overflow into another month. This adjustment is part of what makes EDATE useful for schedules based on monthly intervals.

EDATE moves by whole months. If its month argument is a decimal, Excel rounds that argument down to an integer before calculating. For clarity, use a whole-number month count in the cell or formula, rather than relying on a decimal to represent a partial month. A partial month is a different requirement and may need a day-based calculation or a stated business rule.

Entering the Formula in a Worksheet

For a simple worksheet, put the original dates in column A and the results in column B. Select B2, enter the formula, and press Enter. To calculate the same interval for more rows, drag the fill handle at the lower-right corner of B2 down the column, or copy and paste the cell. Excel adjusts the row reference as the formula is filled down.

  1. Enter a real Excel date in A2, such as 5/31/2025, and check that Excel recognizes it as a date.
  2. In B2, enter =EDATE(A2,-3) to calculate the date three months earlier.
  3. Format B2 as a date if it appears as a number. Use Home > Number Format and choose Short Date or another date display you prefer.
  4. Copy the formula down if each row has its own starting date.

In Microsoft 365 for Windows, the formula and ribbon steps above apply. EDATE is also available in current Excel for Mac and Excel for the web, and the formula syntax is the same. Ribbon layout can vary slightly by platform, but date formatting is available through the Number Format controls. If you enter a date that Excel has stored as text, correct the input or convert it to a real date before relying on the result.

Examples, Including Dates at Month-End

These examples show why calendar-month arithmetic differs from subtracting a set number of days. In each case, the result depends on the actual date in the starting cell.

  • With May 31, 2025 in A2, =EDATE(A2,-1) returns April 30, 2025, because April has no 31st.
  • With May 31, 2025 in A2, =EDATE(A2,-3) returns February 28, 2025. The destination month is not a leap-year February.
  • With March 31, 2024 in A2, =EDATE(A2,-1) returns February 29, 2024. The leap-year day is valid, so Excel can preserve the 29th.
  • With January 30, 2025 in A2, =EDATE(A2,-1) returns December 30, 2024, because December has a 30th.

Each formula in that list assumes the stated starting date is in A2. If your dates are in another cell, replace A2 with the correct reference. These examples also show that EDATE does not always return the final day of a month: it adjusts only when the original day number does not exist in the destination month.

Choosing Between EDATE and EOMONTH

Use EDATE when you want to preserve the day number where possible. Use EOMONTH when the result should always be the final calendar day of a month. These functions are related, but they answer different questions. For an invoice dated on the 12th, one month earlier is usually the 12th; for a report that must cover full calendar months, the relevant target may instead be the last day of the previous month.

If A2 contains a date and you need the last day of the month three months before it, use =EOMONTH(A2,-3). For example, if A2 is May 12, 2025, the result is February 28, 2025. If A2 is May 12, 2024, the result is February 29, 2024. EOMONTH does not preserve the starting day; it deliberately returns a month-end date.

A useful way to choose is to ask what the date represents. A recurring appointment or anniversary often needs EDATE, with a decision about how to handle dates such as the 29th, 30th, or 31st. A month-end close or a period boundary often needs EOMONTH. Neither function is universally better; the intended business rule determines the right result.

Why Not Subtract 30 Days?

Subtracting 30 from a date subtracts 30 days, not one calendar month. That can produce an unexpected date because months range from 28 to 31 days. For example, going back 30 days from May 31, 2025 does not land on April 30; it lands on May 1, 2025. If your instruction says “one month earlier,” EDATE is generally the closer match to ordinary calendar meaning.

Fixed-day subtraction is appropriate when the requirement is explicitly a number of days, such as a 30-day review window. It is not interchangeable with monthly scheduling. Likewise, dividing a month into a fixed number of days can create inconsistent results around month boundaries. Decide whether the interval is defined in calendar months or in days before choosing a formula.

Common Problems and How to Fix Them

  • The result appears as a number. Excel dates are stored as serial numbers. Apply a date format using Home > Number Format > Short Date. Formatting changes how the value displays, not the underlying calculation.
  • The formula returns an error or an unexpected date. Check that the starting cell contains a valid date value, not text that only looks like a date. Regional settings affect how Excel interprets typed dates, so an ambiguous entry such as 3/4/2025 may be read differently in different locales.
  • A month-end date changes to the 28th, 29th, or 30th. This is expected when the destination month lacks the original day. If every result must be month-end, use EOMONTH instead.
  • The result seems to move by the wrong number of months. Check the sign and the month-count cell. A negative count moves backward; a positive count moves forward. If B2 stores the number to subtract, use the negative of that value in the EDATE calculation.
  • The output includes an unexpected time. Excel dates can include a time fraction. If the starting value contains a time, the returned value may retain it. Use a date-only value or apply a display format that hides the time when appropriate.

A further point of confusion is the difference between changing a date and changing how it is displayed. Choosing a different date format cannot fix a wrong calculation, and changing the formula does not necessarily change the display. Check the value and the number format separately when a result looks incorrect.

Frequently Asked Questions

How do I subtract six months from a date in Excel?
With the starting date in A2, enter =EDATE(A2,-6). Excel returns the date six calendar months earlier, adjusting the day if that date does not exist in the destination month.

How do I subtract a different number of months for each row?
With starting dates in A2:A20 and positive month counts to subtract in B2:B20, enter =EDATE(A2,-B2) in C2, then fill it down. Each row uses its own date and month count.

Does EDATE always keep the same day of the month?
It keeps the day number when that day exists in the destination month. If it does not, Excel returns the last valid day of that month. For example, a move from May 31 to the previous month returns April 30.

How can I get the last day of the month three months earlier?
With the starting date in A2, use =EOMONTH(A2,-3). This returns the final day of the month three calendar months before the starting date, regardless of its day number.

Why does Excel show a number instead of a date?
The calculation may be correct while the result cell uses a General or numeric format. Select the result, then choose Home > Number Format > Short Date. If that does not help, confirm that the source value is a valid Excel date rather than text.

Use the Formula That Matches the Rule

For most requests to subtract months from an Excel date, EDATE with a negative month count is the direct solution. It handles different month lengths and leap years without converting the interval into a guessed number of days. Use EOMONTH instead when the required output is specifically a month-end date, and verify that your input cells contain real dates. Once those choices are clear, the calculation is simple to copy across a schedule or down a list.

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.