Date & Time
Get the Same Calendar Date Next Month or Next Year Without Overflow (EDATE vs DATE)
Roll subscription billing and anniversary dates forward by 1, 3, or 12 months with EDATE so Jan 31 and Feb 29 leap-day anchors clamp to Feb 28 instead of spilling into March.
Why DATE(YEAR(d), MONTH(d) + 1, DAY(d)) overflows Jan 31 into March 3
Advancing a date with DATE(YEAR(B2), MONTH(B2) + 1, DAY(B2)) evaluates DATE(2026, 2, 31) when B2 is 2026-01-31—which rolls three days past Feb 28 and bills the customer on March 3! The same bug turns a leap-day signup (2024-02-29) into March 1, 2025 under DATE(YEAR(B2) + 1, MONTH(B2), DAY(B2)).
EDATE(B2, months) preserves the exact day-of-month whenever the target month is long enough, and automatically clamps short months (including non-leap Februaries) to the last valid calendar day of that target month.
| Start Date | Target Rollover | DATE(...) Overflow Result | EDATE(...) Clamped Result |
|---|---|---|---|
| 2026-01-31 | +1 month | 2026-03-03 (overflows Feb!) | 2026-02-28 |
| 2024-02-29 (Leap) | +12 months (+1 yr) | 2025-03-01 (overflows Feb!) | 2025-02-28 |
| 2026-04-15 | +1 month / +12 months | 2026-05-15 / 2027-04-15 | 2026-05-15 / 2027-04-15 |
Month & Annual Anniversary Rollover Formula
Use EDATE(start_date, 1) for monthly renewals, EDATE(start_date, 3) for quarterly renewals, and EDATE(start_date, 12) for annual renewals.
=EDATE(B2, 1)Try the example
Use the original values or change them to test your understanding.