Retirement Date Formula

Last verified: 18 November 2026

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

Prefer the tool to the spreadsheet? The retirement date calculator runs the same maths in your browser — birth date plus retirement age, nothing to download.

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.

Free template download

Download the Excel template (.xlsx)

Free to keep and share. No macros, no sign-up. Works in Excel and Google Sheets (upload the file to Drive and open with Sheets).

How to use it

  1. Enter your birth day, month and year in the yellow cells.
  2. Enter your retirement age in years (and months, if you like).
  3. Read your retirement date — on your birthday and at month-end — plus the weekday and days from today.

The formulas inside

  • Retirement date: =EDATE(DATE(B6,B5,B4),B8*12+B9)
  • Last day of the month: =EOMONTH(EDATE(DATE(B6,B5,B4),B8*12+B9),0)
  • Weekday: =TEXT(B11,"dddd")
  • Days from today: =B11-TODAY()

Locale note: "dddd" is the English-Excel format code. In a non-English Excel, TEXT needs your locale's weekday code instead — or format the date cell directly with a long date format to see the weekday.

Worked examples

Born 15 March 1985, retiring at 60:

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

Born 29 February 1964, retiring at 60:

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

2024 is a leap year, so 29 February exists — no clamping happens.

Born 29 February 1964, retiring at 61:

=EDATE(DATE(1964,2,29), 61*12)   →   28 February 2025 (Friday)

2025 is not a leap year, so EDATE clamps to the last day of February.

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 March + 1 month → 30 April, 29 February → 28 February in non-leap years) exactly the way the calculators on this site do.

The India 1st-of-month rule as a formula

India's central government rule (FR 56(a)) retires staff on the last day of the month they turn 60 — or the last day of the previous month if they were born on the 1st. With the birth date in A1, this one formula applies the whole rule for age 60:

=IF(DAY(A1)=1, EOMONTH(EDATE(A1, 720), -1), EOMONTH(EDATE(A1, 720), 0))

720 is 60 × 12 months — change it for another age (696 for 58, 744 for 62). The IF tests whether the birth day is the 1st: if so, EOMONTH(…, -1) steps back to the previous month-end; otherwise EOMONTH(…, 0) gives the birthday month's last day. Checked against the rule: a birth date of 1 June 1965 returns 31 May 2025, and 15 March 1985 returns 31 March 2045 — the same dates the India calculator gives.

Sources: the formula behaviour is that of the Excel and Google Sheets EDATE and EOMONTH functions, with the month-end clamping convention from How to Calculate Your Retirement Date.

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.

Is the template really free?

Yes — download it, keep it, share it with colleagues. No account, no payment, no macros phoning home.

Does it work in Google Sheets or LibreOffice?

Google Sheets: yes — upload the .xlsx to Drive and open with Sheets; EDATE, EOMONTH and TEXT all work. LibreOffice Calc: yes, the same functions exist.

Does the template handle leap years?

Yes — EDATE clamps to the last valid day of the month when the exact date doesn't exist. For example, someone born 29 February 1964 retiring at 60 gets 29 February 2024 (Thursday) (2024 is a leap year — no clamping), while retiring at 61 gives 28 February 2025 (Friday) (2025 is not a leap year, so it clamps to the last day of February).

Can I adapt it for my scheme's rules?

Yes — it's just formulas. Add your scheme's age, or an EOMONTH wrapper for month-end conventions like India's 1st-of-month rule.

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. Warning: Microsoft documents the "MD" option as unreliable — it can return incorrect results. A safer approach for days is =target_date - EDATE(birth_date, DATEDIF(birth_date, target_date, "Y")*12 + DATEDIF(birth_date, target_date, "YM")), which subtracts the whole years and months first and counts the leftover days directly.