Retirement Date Formula

The retirement date formula for Excel, Google Sheets and pen-and-paper: birth date plus retirement age, with month-end handling done right.

The formula

Retirement date = birth date + retirement age, where the age is added in months and the day is clamped to the month's end.

Excel / Google Sheets

With birth date in A1 and retirement age in years in B1:

=EDATE(A1, B1*12)

For years and months (years in B1, months in C1):

=EDATE(A1, B1*12 + C1)

For the last day of the retirement month (month-end conventions):

=EOMONTH(EDATE(A1, B1*12), 0)

Pen and paper

  1. Add the years to the birth year; add the months, carrying into years.
  2. Keep the same day of the month — unless that day doesn't exist (e.g. 29 February), in which case use the month's last day.

Worked example

Born 15 March 1985, retiring at 60:

=EDATE(DATE(1985,3,15), 60*12)   →   15 March 2045

Born 29 February 1964, retiring at 60:

=EDATE(DATE(1964,2,29), 60*12)   →   28 February 2024   (clamped)

Why EDATE and not simple addition?

Adding years to a date isn't just year arithmetic — months have different lengths and leap years exist. EDATE handles the month rollover and clamps impossible days (31 April → 30 April, 29 February → 28 February) exactly the way the calculators on this site do.

Related

Found an error? Report it

Frequently asked questions

Why does EDATE clamp 29 February to 28 February?

Because 29 February doesn't exist in non-leap years — EDATE returns the last valid day of the month. This matches the standard convention used across this site.

How do I get the weekday in Excel?

=TEXT(A1,"dddd") gives the full weekday name. Every calculator on this site shows the weekday for the same reason — it catches off-by-one mistakes.

How do I compute the age on a date in Excel?

=DATEDIF(birth_date, target_date, "Y") for whole years, "YM" for remaining months, "MD" for remaining days.