Excel Date Minus 1 Month? Formula and Date Rules

Formulas & functions · Updated November 18, 2026

To subtract one calendar month from an Excel date, use =EDATE(A1,-1), where A1 contains a real Excel date. EDATE keeps the day number when possible and returns the last day of the previous month when that day does not exist there.

The basic formula

In Microsoft Excel for Microsoft 365 on Windows, enter the date in A1, then put the formula in another cell, such as B1. For example, if A1 is 15 June 2025, the result is 15 May 2025. The second argument, -1, tells EDATE to move one month backward; use 1 to move one month forward.

For a date stored in A2 instead, adjust the cell reference. With the source date in A2, the formula is =EDATE(A2,-1). You can fill it down a column to calculate a previous-month date for each row. EDATE is included in current Excel; you do not need to install an add-in.

The result is an Excel date value, not just a label. You can compare it with other dates, use it in calculations, or format it to display as a date. If the cell shows a number such as 45823, the calculation may still be correct: Excel stores dates as serial numbers, and the result cell likely needs date formatting.

What “one month earlier” means

A calendar month is not a fixed number of days. Months have 28, 29, 30, or 31 days, so subtracting one month is different from subtracting 30 days. EDATE follows calendar-month logic: it shifts the month while preserving the day number if that day exists in the destination month.

That rule matters at the end of a month. If A1 contains 31 March 2025, then =EDATE(A1,-1) returns 28 February 2025. February has no 31st day, so Excel uses February’s last day. If A1 contains 31 March 2024, the result is 29 February 2024 because 2024 is a leap year.

This behavior is often useful for billing cycles, monthly reporting, and date-based deadlines. It is also a common source of surprise if you expected every calculation to preserve a day number or to count a fixed number of days. Decide which meaning your work requires before choosing a formula.

Examples, including month ends

Suppose dates are in A2:A5 and you enter the previous-month formula in B2, then copy it down. The results illustrate how EDATE deals with ordinary dates and short months:

  • 15 June 2025 becomes 15 May 2025.
  • 1 March 2025 becomes 1 February 2025.
  • 31 March 2025 becomes 28 February 2025.
  • 31 March 2024 becomes 29 February 2024.

For a recurring schedule, keep the original date as the reference when you want each result to be one month before that original date. If you instead calculate each next date from the previous result, month-end dates can continue to follow the adjusted day. For example, stepping back from 31 March to 28 February and then back another month gives 28 January, not 31 January. That is because the second calculation starts with 28 February.

To calculate several months before the same original date, use the month count as a cell value. With the original date in A2 and the number of months to subtract in B2, use =EDATE(A2,-B2). If B2 is 3 and A2 is 31 May 2025, the result is 28 February 2025. A negative month count reverses direction, so this setup can also move dates forward if that is appropriate to the worksheet.

Choose the right calculation

EDATE is the most direct choice when “one month” means a calendar month. Other formulas can be better when the requirement is specifically a fixed number of days or the last day of a month. These distinctions are important in schedules and rules that define dates precisely.

  • Previous calendar month, same day where possible: use EDATE with -1.
  • Exactly 30 days earlier: subtract 30 from the date. This may land in a different calendar month than EDATE.
  • Last day of the previous month: use EOMONTH with -1.
  • Same day number, allowing Excel’s date rollover: DATE can construct a date, but it may roll into a later month when the day is unavailable.

For example, with 31 March 2025 in A1, subtracting 30 days gives 1 March 2025, while subtracting one calendar month with EDATE gives 28 February 2025. Neither result is universally more correct; it depends on whether your rule is defined in days or calendar months.

When you need the previous month’s last day

Some worksheets need month-end dates rather than a date corresponding to the source day. EOMONTH returns the final day of a month a specified number of months from the start date. With a date in A1, =EOMONTH(A1,-1) returns the last day of the prior month. For 12 June 2025, that is 31 May 2025; for 1 March 2025, it is 28 February 2025.

This is a useful alternative for month-end balances, closing reports, and period boundaries. EDATE and EOMONTH are not interchangeable: EDATE aims to preserve the day number, while EOMONTH deliberately returns the month’s final day. If the source date is already at month end, both can sometimes produce the same result, but that does not make their general behavior identical.

If a custom formula is necessary, DATE can be combined with MIN to cap the day at the previous month’s last day. With a valid date in A1, use =DATE(YEAR(A1),MONTH(A1)-1,MIN(DAY(A1),DAY(EOMONTH(A1,-1)))). This also returns 28 February 2025 for 31 March 2025. It is longer than EDATE and usually unnecessary, but it makes the “same day if possible, otherwise last day” rule explicit.

Enter the formula and display the result

To set up a simple worksheet in Excel for Microsoft 365 on Windows:

  1. Enter a date in a cell, such as A1. Use a value Excel recognizes as a date, not a text string that merely looks like one.
  2. Select the result cell and type =EDATE(A1,-1), then press Enter.
  3. If the result appears as a serial number, select the result cell and choose a date format from Home > Number Format.
  4. For a column of dates, copy the formula down, or drag the fill handle at the lower-right corner of the formula cell.

You can also open Format Cells with Ctrl+1 on Windows and choose a date format. On Mac, use Command+1 to open Format Cells; ribbon labels and layout can differ slightly by Excel version. Excel for the web supports EDATE as well, though the available formatting controls and ribbon layout may look different from the Windows desktop app.

Formatting changes how a date is displayed, not the underlying date value. For instance, a cell might show 5/15/2025, 15-May-25, or May 2025 depending on its number format. A custom display that shows only a month and year can make a date look like the first of a month or hide its day, so check the formula bar or apply a full date format when verifying results.

Common problems and how to fix them

If Excel returns #VALUE! or an unexpected result, first check the source cell. EDATE requires a usable start date. A cell containing text, an invalid date, or a date entered in a format Excel does not interpret as intended can cause trouble. Regional settings affect how ambiguous entries such as 4/5/2025 are read: one system may interpret that as April 5, another as May 4. Enter dates unambiguously or use a consistent date format for the workbook.

If you see a five-digit number, apply a date format rather than changing the formula. Excel stores dates as serial values, and number formatting controls whether the value appears as a date or as a number. Conversely, applying a date format cannot turn arbitrary text into a valid date; the source itself must be recognized correctly.

Another frequent mistake is using =A1-30 when the requirement says “one month ago.” That formula subtracts exactly 30 days. It can be appropriate for a 30-day interval, but not for a calendar month. Likewise, manually subtracting 1 from the month part of a date can fail around January, leap years, and month ends unless the formula accounts for those cases.

Finally, check whether you are calculating from the original date or chaining from a previous result. Chained month-end calculations can drift to a day earlier in later months because EDATE uses each adjusted result as its new starting point. Referencing the original date avoids that behavior when every row is intended to represent a fixed offset from the same date.

Frequently Asked Questions

What formula subtracts one month from a date in Excel?
With the date in A1, enter =EDATE(A1,-1). It moves back one calendar month and adjusts to the last day of the destination month if the original day does not exist there.

How do I subtract one month from today’s date?
With no source cell needed, use =EDATE(TODAY(),-1). TODAY updates with the current date when Excel recalculates, so the result changes over time. Format the result cell as a date if it appears as a serial number.

Does Excel minus one month mean minus 30 days?
No. EDATE moves by calendar months, and months have different lengths. For exactly 30 days earlier, with a date in A1, use =A1-30; for a calendar month earlier, use EDATE.

What happens when I subtract a month from March 31?
EDATE returns the last day of February: 28 February in a common year or 29 February in a leap year. It does not return a date in March or treat every month as 30 days.

How do I get the last day of the previous month?
With the source date in A1, use =EOMONTH(A1,-1). This returns the prior month’s final date regardless of the day number in A1.

Why does my result show a number instead of a date?
Excel dates are stored as serial numbers. Select the result cell and apply a date format through Home > Number Format or Format Cells; the formula can be correct even when the display is not what you expect.

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.