Date & Time
Add years to a date: why EDATE(date, years*12) beats DATE on Feb 29 leap days
Compare EDATE(start, N*12) against DATE(YEAR(start)+N, MONTH(start), DAY(start)) for contract renewals and expiration dates.
What happens to a Feb 29 contract 1 year later?
For valid dates other than February 29, EDATE(A2, B2 * 12) and DATE(YEAR(A2) + B2, MONTH(A2), DAY(A2)) return the exact same anniversary date.
On a leap-day start date (2024-02-29) plus 1 year, they diverge: EDATE(2024-02-29, 12) clips to the end of February (2025-02-28), whereas DATE(2025, 2, 29) overflows 1 day into March (2025-03-01)! For subscriptions, warranties, and compliance deadlines, EDATE(start, years * 12) keeps renewals inside the original calendar month.
Month-end clipping is a calculation choice, not a universal rule for contracts or deadlines. Select February 28 or March 1 according to the applicable agreement or requirement. YearsToAdd is an integer in this example.
| StartDate (A) | YearsToAdd (B) | EDATE(A, B*12) | DATE(YEAR+B, M, D) |
|---|---|---|---|
| 2024-02-29 | 1 | 2025-02-28 (Feb clipped) | 2025-03-01 (Mar overflow) |
| 2024-02-29 | 4 | 2028-02-29 (next leap yr) | 2028-02-29 |
Add or subtract years with EDATE(A2, B2 * 12)
Multiplying years by 12 months handles positive future renewals (+1, +4) and negative lookbacks (-2 years = -24 months) in one formula.
=MONTH(EDATE(A2, B2 * 12)) & "-" & DAY(EDATE(A2, B2 * 12))Contrast DATE() March 1 overflow on non-leap years
DATE(YEAR(A2) + 1, MONTH(A2), DAY(A2)) rolls 2025-02-29 to March 1 ("3-1").
=MONTH(DATE(YEAR(A2) + B2, MONTH(A2), DAY(A2))) & "-" & DAY(DATE(YEAR(A2) + B2, MONTH(A2), DAY(A2)))Try the example
Use the original values or change them to test your understanding.