EDATE in Excel: Add or Subtract Months from a Date

In Excel, EDATE returns a date that is a specified number of months before or after a starting date. Use the syntax =EDATE(start_date, months): for example, if A2 contains January 31, 2024, =EDATE(A2,1) returns February 29, 2024.

What EDATE does

EDATE is useful when a date needs to move by calendar months rather than by a fixed number of days. A value of 1 moves the date one month forward, 3 moves it three months forward, and -1 moves it one month backward. The function returns an Excel date serial number, which Excel can display as a date when the result cell has a date format.

This makes EDATE a practical choice for recurring dates such as invoice due dates, subscription renewals, review milestones, and payment schedules. A month is not always the same length: depending on the month and year, it has 28, 29, 30, or 31 days. EDATE follows the calendar rather than treating every month as 30 days.

Syntax and arguments

The function has two arguments: start_date is the date from which to count, and months is the number of months to move. Both can be cell references, or the month offset can be a number entered directly. A positive offset moves forward; a negative offset moves backward. Excel rounds a fractional months argument down to an integer, so use a whole number when you want a predictable month shift.

With a starting date in A2 and a month offset in B2, enter =EDATE(A2,B2). If A2 contains March 15, 2025 and B2 contains 4, the result is July 15, 2025. If B2 is -2 instead, the result is January 15, 2025. Keeping the offset in its own cell makes it easy to change the schedule without editing the formula.

For a one-off calculation, you can put the starting date and offset directly into the function. With A2 containing January 15, 2025, for example, enter =EDATE(A2,6) to return July 15, 2025. Using a cell reference for the date is generally safer than typing a date string into a formula, because date text can be interpreted differently under different regional settings.

How EDATE handles month ends

If the destination month does not contain the same day number as the starting date, EDATE returns the last valid day of that destination month. This is often called clipping to the month end. For example, January 31 shifted forward one month becomes February 28 in a non-leap year or February 29 in a leap year. A shift from March 31 back one month becomes February 28 or 29, as appropriate.

That behavior is important when building a sequence. If A2 contains January 31, 2024, and you calculate one month after that date, the answer is February 29, 2024. If you then calculate one month after the February result, the answer is March 29, not March 31. The original day number has been replaced by the valid day in February, so chaining each result can cause dates to drift.

To keep every appointment anchored to the original date, calculate each row from the first date and a growing month offset instead of using the preceding result. With the original date in A2 and the row’s month number in B2, use =EDATE($A$2,B2). For offsets 1 and 2, a January 31, 2024 start produces February 29 and March 31. The dollar signs keep the original-date reference fixed when you fill the formula down.

Build a recurring date schedule

EDATE can generate monthly or multi-month schedules without manually entering every date. Put the first date in A2, then put successive offsets such as 1, 2, 3, and 4 in B2:B5. With A2 holding January 31, 2024 and each offset in column B, enter =EDATE($A$2,B2) in C2 and fill it down. The results are February 29, March 31, April 30, and May 31, 2024.

This setup is often preferable to entering =EDATE(C2,1) in each next row. The latter calculates each date from the preceding result, so an end-of-month schedule may settle on the 28th or 29th after February. Referencing the original start date preserves the intended month-end pattern in later months. If the schedule is supposed to occur on the same numbered day when possible, with shorter months adjusted to their final day, the anchored method expresses that intent clearly.

When you need a different cadence, change the offsets. For a quarterly sequence, use 3, 6, 9, and 12; for a six-month sequence, use 6, 12, and 18. The formula still moves by calendar months, so a date on the 30th or 31st may land on a month’s final day when that month is shorter.

Display and use the returned date

Excel stores dates as serial values, so a result that appears as a number does not necessarily mean the calculation failed. Select the result cell and apply a date format from Home > Number. For more control, open Format Cells and choose a date style, or use a custom format such as mmm d, yyyy to display a result like Feb 29, 2024. The underlying value remains a date, even when the format shows only the month and year.

Formatting changes how the value appears, not the date EDATE calculated. A format such as mmm yyyy can make a schedule easier to scan, but it hides the day of the month. If the exact day matters, include a day format in the display. You can still sort and compare properly stored date results even when the visible format is abbreviated.

Make sure the starting value is a real Excel date, not text that merely looks like one. If the date came from an import, it may be stored as text, especially when its day-month order conflicts with the computer’s regional settings. Re-entering the value in a recognizable date format or converting the imported column to dates can resolve that issue.

EDATE compared with EOMONTH and adding days

EDATE and EOMONTH both work with calendar months, but they answer different questions. EDATE aims for the same day number in the target month where possible, adjusting to that month’s last day when necessary. EOMONTH returns the last day of the target month regardless of the starting date. A date of May 12 shifted one month with EDATE remains June 12; the same start date with EOMONTH ends on June 30.

With a date in A2 and a month offset in B2, use =EOMONTH(A2,B2) when the requirement is always the final calendar day of the month. For example, if A2 is May 12, 2025 and B2 is 1, the result is June 30, 2025. EOMONTH is handy for month-end reporting cutoffs, while EDATE is usually the better fit for a date that recurs on a particular day of the month.

Adding 30 days is not equivalent to adding one month. If A2 contains January 31, 2024, =A2+30 returns March 1, while EDATE moves to February 29. Use day addition for a period defined by an exact number of days; use EDATE when the instruction refers to calendar months. For elapsed time expressed in months, EDATE also does not calculate the number of complete months between two dates; that is a different problem.

Common errors and practical checks

If EDATE returns #VALUE!, check whether the starting value is a valid date Excel can interpret and whether the month argument is numeric. A date imported as unrecognized text is a common cause. If the result is a serial number, apply a date format rather than changing the formula. If the displayed date seems one day off, check the source date and regional date interpretation before adjusting the calculation.

For a reliable workbook, verify a few boundary cases before filling a schedule down: a 31st moving into a shorter month, February in a leap year, and a negative month offset. Also decide whether later dates should be based on the original start date or on the preceding result. That choice determines whether a schedule can drift after a short month.

EDATE can calculate dates beyond the range supported by Excel’s date system, so extremely large offsets may produce an error. It also does not know about business days, holidays, or local rules for moving a due date when it falls on a weekend. Those requirements need an additional rule or a different calculation; EDATE alone simply shifts a calendar date by months.

Frequently Asked Questions

Can EDATE subtract months? Yes. Use a negative number for the months argument. With a date in A2, =EDATE(A2,-3) returns the date three calendar months before it, adjusting to the last valid day if the destination month is shorter.

Why does EDATE show a number instead of a date? Excel stores dates as serial numbers. The formula may be correct while the cell is formatted as General or Number. Apply a date format from Home > Number to display the result as a calendar date.

Does EDATE always return the same day of the month? It returns that day when it exists in the destination month. When it does not, EDATE returns the month’s last day. For instance, a shift from January 31 to February lands on February 28 or 29, not on a nonexistent February 31.

What is the difference between EDATE and EOMONTH? EDATE tries to preserve the starting day number, subject to month-end adjustment. EOMONTH always returns the last day of the target month. Choose based on whether the intended result is a recurring calendar date or a month-end date.

Why does a monthly schedule move from the 31st to the 29th? If each formula uses the previous result as its starting date, February’s shorter length can change the day used by later calculations. Calculate each offset from the original start date to preserve dates such as March 31 after a January 31 start.

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.