Excel EDATE Function: Syntax, Examples and Tips

Q: What happens when EDATE lands on a shorter month?

The result is clipped to the last day of that month. EDATE of 31 January plus one month returns 28 February (29 in a leap year).

Q: How do I add years with EDATE?

Multiply the years by 12: =EDATE(A2,12*5) adds five years and correctly handles 29 February start dates.

Q: Why does EDATE show a five-digit number?

The cell is in General format, so Excel displays the date serial number. Apply a date format to show it as a date.

Part of the free Module 5: Excel Formulas and Functions · Function 25 of 105 · Full Excel course

The Excel EDATE function returns the date that is a given number of months before or after a start date, keeping the same day of the month where possible. It returns a serial number that displays as a date once the cell is formatted. EDATE is the standard tool for renewal dates, loan schedules, warranty expiries and any calculation that moves forward or back in whole months.

Table of Contents

Syntax

=EDATE(start_date, months)

Arguments

Argument	Required / Optional	Meaning
start_date	Required	The date to start from: a date cell, a DATE result, TODAY() or recognisable date text.
months	Required	Number of months to add. Positive values move forward, negative values move back. Decimals are truncated, so 1.9 is treated as 1.

How EDATE works

EDATE changes the month (and year, if necessary) and keeps the day. When the target month is shorter than the original day, the result is clipped to the last day of that month: =EDATE("2026-01-31",1) returns 28 February 2026, and =EDATE("2026-08-31",1) returns 30 September 2026. This behaviour differs from the DATE function, where DATE(2026,2,31) rolls forward to 3 March. Any time portion of the start date is dropped. Text that is not a date returns #VALUE!, and results outside the years 1900 to 9999 return #NUM!.

Step-by-step example: subscription renewal dates

Enter subscription start dates in column A and the term in months in column B (for example 12).
In C2 enter =EDATE(A2,B2) and press Enter.
If C2 shows a number such as 46634, apply a date format with Ctrl+Shift+3.
Fill the formula down. A start date of 31 August 2026 with a 6-month term gives 28 February 2027, clipped to the month end.
In D2 enter =IF(C2<TODAY(),"Expired","Active") to flag lapsed subscriptions.

Practical use cases

1. Loan or EMI payment schedule

=EDATE($B$1,ROW(A1))

With the first payment date in B1, filling this down produces one row per month, always on the same day of the month.

2. Warranty expiry from a purchase date

=EDATE(A2,24)-1

Subtracting 1 makes a 24-month warranty bought on 4 September end on 3 September two years later.

3. Retirement date at age 60

=EDATE(BirthDate,60*12)

4. Same day last year for comparisons

=EDATE(TODAY(),-12)

5. First day of the month, three months ahead

=EDATE(A2-DAY(A2)+1,3)

Common mistakes and tips

Result looks like a number: EDATE returns a serial number. Format the cell as a date.
Expecting DATE-style rollover: EDATE clips to month end; DATE rolls into the next month. Choose the one that matches the business rule.
Adding days or years: EDATE works only in months. Add days with plain arithmetic and years with EDATE(date,12*years).
#VALUE! error: start_date is text Excel cannot interpret. Convert it with DATEVALUE or DATE.
Excel 2003 and earlier: EDATE needed the Analysis ToolPak; since Excel 2007 it is built in.

EDATE compared with EOMONTH and DATE arithmetic

EDATE and EOMONTH take the same two arguments and shift a date by months; the difference is the day that comes back. EDATE keeps the original day (clipped if necessary), so 15 January plus one month is 15 February. EOMONTH always returns the last day of the target month, so the same input gives 28 February. Use EDATE for anniversaries, instalments and renewals, and EOMONTH for period ends, closing dates and month-based grouping. DATE(YEAR(A2),MONTH(A2)+n,DAY(A2)) is a third option that rolls excess days forward instead of clipping, which is occasionally required by contract wording. For working-day offsets rather than months, WORKDAY is the correct function.