The Excel EDATE function, explained interactively.
EDATE shifts a date by a whole number of months. The name is misleading — it’s not related to email or enumeration; it’s the month-arithmetic tool that knows Feb doesn’t have a 31st. Pair it with EOMONTH for renewal schedules, TODAY for rolling anniversaries, and DATE for ad-hoc pivots.
How to use EDATE
- Type
=EDATE(and supply the start_date — a cell reference, a literal date (DATE(2026, 4, 21)), or any formula that returns a real Excel date. - Add a comma and months — the integer offset. Positive shifts forward, negative shifts backward, zero returns the original date.
- Close the parenthesis. The result may show as a serial number — Format Cells → Date to render as a calendar date.
- For month-end arithmetic specifically (always land on the last day), reach for EOMONTH instead. EDATE preserves the source day-of-month where possible.
In the demo below, the amber-highlighted start dates are the ones where the clamp kicks in: Jan 31, Aug 31, and Feb 29 (leap day). Flip the months picker to see how those rows land on the last day of the target month instead of overflowing forward.
DATEVALUE or DATE to coerce if needed.0 returns the original date. Decimals are truncated (1.9 → 1). No upper limit in practice.| A | B | D | |
|---|---|---|---|
| 1 | Customer | Start date | +12m |
| 2 | Ava | 2026-01-15 | 2027-01-15 |
| 3 | Ben | 2026-01-31 | 2027-01-31 |
| 4 | Chloe | 2025-08-31 | 2026-08-31 |
| 5 | Dev | 2025-12-01 | 2026-12-01 |
| 6 | Eva | 2024-02-29 | 2025-02-28 |
=EDATE(B2, 12). months = 12: EDATE shifts every start date 12 months forward. Watch rows 3-4 (Jan 31, Aug 31) — when the target month has fewer days than the source, EDATE clamps to the last available day (not February 31). Leap day (row 6, 2024-02-29) behaves the same way: +12 months lands on Feb 28 in 2025.EDATE syntax and arguments
Two required arguments, no optionals. See Microsoft’s official EDATE reference for the canonical specification.
DATE() result, or any formula returning a date serial number. Text-stored dates (like "2026-04-21") fail with #VALUE!; wrap with DATEVALUE to coerce.0 returns the source date. Decimal values are truncated toward zero (2.9 becomes 2). No practical upper or lower limit — EDATE handles multi-year and cross-century shifts natively.EDATE examples
Four patterns that cover most real-world EDATE use.
Example 1: EDATE — next monthly renewal
The canonical case. Shift a start date by one month to compute the next renewal without worrying about month length.
A2 = 2026-01-31 → returns 2026-02-28 (clamped, because February has no 31st). The same formula on 2026-02-28 returns 2026-03-28 — EDATE preserves the day-of-month where possible. This is why calendar-app subscriptions renew on the last of the month once they hit February.
Example 2: EDATE — anniversary / year-over-year
Compute the date exactly N years ago by shifting by a negative month multiple.
Returns today’s date one year ago. Use as an anchor for YoY sales comparisons: SUMIFS(sales, date, ">=" & EDATE(TODAY(), -12), date, "<" & TODAY()). Safer than TODAY() - 365 because it correctly handles leap years.
Example 3: EDATE — loan / mortgage maturity
Compute a term-end date from origination + term months. Works for any fixed-term financial product.
With A2 the origination date and B2 the term in months (e.g. 360 for a 30-year mortgage). No leap-year drift because EDATE is month-based, not day-based. Combine with EOMONTH if the term always ends on the last business day of the final month.
Example 4: EDATE — build a renewal schedule
Generate a sequence of future renewal dates by applying EDATE with SEQUENCE (Excel 365 / Sheets). One formula spills the whole schedule.
Returns 12 rows: month-1 renewal, month-2 renewal, through month-12 renewal. Set SEQUENCE(12, 1, 0) if you want the original date plus 11 renewals. Works identically in Google Sheets and modern Excel.
Common EDATE errors and fixes
Four failure modes, each with what to check and how to recover.
EDATE returns #VALUE!
Cause: start_date is text, not a real date. Imported CSV dates, pasted strings, and cells formatted as text but containing what looks like a date all fail.
Wrap with DATEVALUE(): =EDATE(DATEVALUE(A2), 12). Or convert the column in place via Data → Text to Columns → Date format. Check alignment: real dates right-align, text dates left-align.
EDATE returns a number like 46782
Cause: the output cell is formatted as Number or General. The serial number is correct; it just isn’t being rendered as a date.
Select the cell → Format Cells → Date → pick a format. Fast path: Ctrl+Shift+# (Windows) / Ctrl+Shift+3 (Mac) applies the default date format. Excel remembers the format on copy-paste, so formatting once usually suffices.
EDATE gives the wrong end-of-month date
Cause: EDATE preserves the source day-of-month, not the target month’s last day. If you want every result to land on the last day, EDATE is the wrong tool.
Switch to EOMONTH: =EOMONTH(A2, 1) always returns the last day of the month N months away, regardless of source day. EDATE and EOMONTH coincide only when the source is already a month-end.
EDATE result is off by one month on Jan 31
Cause: confusion between clamping and overflowing. EDATE(Jan 31, 1) returns Feb 28 (clamp), not Mar 3 (overflow). This surprises users coming from systems that add 30 days instead of 1 calendar month.
This is correct EDATE behaviour — it matches how calendar apps schedule recurring events. If you genuinely want day-based arithmetic, use A2 + 30 instead. For “last day of next month”, EOMONTH.
EDATE vs EOMONTH, DATE & DATEDIF
Four date-manipulation tools. Pick by whether you’re shifting, locking to month-end, building from scratch, or measuring an elapsed interval.
| Function | Returns | Args | Typical use |
|---|---|---|---|
| EDATE | Same day-of-month, N months away | start_date, months | Subscription renewals, anniversaries, loan maturity |
| EOMONTH | Last day of month, N months away | start_date, months | Month-end close dates, billing cutoffs, reporting periods |
| DATE | Specific date from y/m/d | year, month, day | Build dates from columns, month-overflow trick |
| DATEDIF | Interval between two dates | start, end, unit | Age in years, tenure in months, gap in days |
Rule of thumb: EDATE for “same-day, N months away”; EOMONTH for “last-day, N months away”; DATE when you’re building a date from scratch. DATEDIF is the opposite direction — measuring an interval rather than computing a new date. All four handle leap years and cross-century shifts correctly.
EDATE frequently asked questions
6.01What does Excel do when EDATE crosses a shorter month?▸
EDATE clamps to the last day of the target month. EDATE(Jan 31, 1) returns Feb 28 (or Feb 29 in leap years), never March 3. The rule: if the day-of-month in the source doesn’t exist in the target month, Excel uses the target month’s last day instead. This is the main reason EDATE exists — it handles month-end arithmetic correctly where naïve day arithmetic would overflow.
6.02EDATE vs EOMONTH: what’s the difference?▸
EDATE returns the same day-of-month N months away. EOMONTH returns the last day of the month N months away regardless of the source day. For a renewal scheduled on the 15th, use EDATE. For a month-end close that always needs to land on the last day, use EOMONTH. Both clamp correctly on short months; they differ in intent.
6.03Can EDATE take a negative number of months?▸
Yes — negative months shift backward in time. EDATE(TODAY(), -12) returns today’s date one year ago, useful for year-over-year comparisons or anniversary calculations. Negative zero returns the original date (same as 0). The function handles year and decade boundaries automatically.
6.04Why does EDATE show a number instead of a date?▸
The cell is formatted as Number or General. Excel stores dates internally as serial numbers; EDATE returns that integer, but a date format is what renders it as YYYY-MM-DD. Select the cell → Format Cells → Date → pick a format. Or apply Ctrl+Shift+# on Windows for the default date format.
6.05Does EDATE work on text-stored dates?▸
No — EDATE needs a real Excel date (right-aligned, serial-number backed). Text like "2026-04-20" returns #VALUE!. Wrap with DATEVALUE to convert first: =EDATE(DATEVALUE(A2), 12). Or use Data → Text to Columns → Date format to convert the column in place. A common symptom of this is EDATE working on some rows and failing on others — the failing rows have text dates.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.