Calculate a Retirement Date From a Birth Date in Excel

To calculate a retirement date in Excel, add the retirement age, in months, to the birth date with EDATE: =EDATE(B2,12*$F$1). Keep the retirement age in its own cell, because the right age depends on the person's country, pension scheme or contract, and it can change. This guide is for HR, payroll and finance staff who plan headcount or pensions, and it covers exact birthdays, month-end rules, leap-day births and time left.

Set up the sheet#

Put names and birth dates in columns, and the retirement age in a single cell. The examples use this sheet:

The 65 in F1 is only an example. Use the age that applies to your people, and check it against the official rules or policy before you rely on the dates. If different groups retire at different ages, put the age in a column next to each person instead.

The birth dates must be real Excel dates, not text. If they came from another system, see the date basics lesson for how Excel stores dates as serial numbers.

Calculate a retirement date with EDATE#

EDATE adds a number of months to a date and keeps the same day of the month. Since an age is in years, multiply it by 12:

=EDATE(B2,12*$F$1)

1. Click C2 and type the formula.
2. Press Enter. You may see 48684, which is how Excel stores 15 April 2033.
3. Format the cell as a date: Ctrl+Shift+# on Windows, ⌃⇧# on a Mac, or Format Cells > Date.
4. Fill the formula down to C4 (Windows: Ctrl+D; Mac: ⌘D).

The $ signs in $F$1 keep the reference fixed as you fill down. Without them, C3 would look at F2, which is empty, and return Ben's birth date.

The EDATE function guide explains each argument, and the article on adding days, months and years to a date shows other ways to shift dates.

Retire at the end of the month or the start of the next#

Many policies don't use the exact birthday. They use the last day of the birthday month, or the first day of the next month. EOMONTH handles both:

EOMONTH moves forward the given number of months and then jumps to the last day of that month. Adding 1 lands on the 1st of the following month. See the EOMONTH function guide and the side-by-side comparison in EDATE and EOMONTH explained.

What happens with a 29 February birthday?#

EDATE moves a leap-day birthday to 28 February when the target year has no 29 February. Ben was born on 29 February 1964, and 2029 is not a leap year, so =EDATE(B3,12*$F$1) returns 28-Feb-2029.

Some people build the date from its parts instead:

=DATE(YEAR(B3)+$F$1,MONTH(B3),DAY(B3))

This returns 1-Mar-2029, because DATE rolls 29 February 2029 over into March. Both formulas are valid. The point is to pick the one that matches your policy and use it for everyone, so nobody's date is a day off.

Calculate the time left until retirement#

DATEDIF counts complete years and months between two dates. With the retirement date in C2:

=DATEDIF(TODAY(),C2,"y")&" years, "&DATEDIF(TODAY(),C2,"ym")&" months"

TODAY() changes every day, so the answer changes too. To make a report that stays fixed, use a set "as of" date instead. As of 3 November 2026, =DATEDIF(DATE(2026,11,3),C2,"y") returns 6 and the "ym" version returns 5, so Ana has 6 years and 5 months to go. For days, simply subtract: =C2-DATE(2026,11,3) gives 2,355 days.

DATEDIF returns #NUM! if the first date is later than the second, which happens for anyone already past their retirement date. Wrap it in IF to show a label instead:

=IF(C2<=TODAY(),"Retired",DATEDIF(TODAY(),C2,"y"))

The same DATEDIF pattern works for current age and length of service, covered in how to calculate age and tenure and age in years and months.

Ages with months, and the retirement year#

Some rules use an age such as 66 years and 4 months. Put the years in F1 and the extra months in G1, then add both:

=EDATE(B2,12*$F$1+$G$1)

You can also type a decimal age such as 62.5 in F1. EDATE then adds 750 months. Be careful with decimals that are not whole months, because EDATE ignores any part of a month.

If you only need the year, add the age to the birth year: =YEAR(B2)+$F$1 returns 2033 for Ana. To count how many people retire in a given year, combine that with COUNTIF, a common step in HR analytics in Excel.

Common mistakes#

These are the slips that give wrong retirement dates:

1. Adding years instead of months. =EDATE(B2,65) adds 65 months, not 65 years. Multiply by 12.
2. Adding days. =B2+65*365 drifts by a day or more because of leap years. Use EDATE.
3. Forgetting the $ signs. Without $F$1, the age cell moves as you fill down.
4. Text dates. A birth date stored as text gives #VALUE!. Convert it with DATEVALUE or Text to Columns.
5. Hard-coding the age. =EDATE(B2,780) hides the rule inside the formula. A labelled age cell is easier to check and update.

Practise it#

EDATE and EOMONTH are quick to learn once you have typed them a few times. Practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet: you write the formula and it checks the date instantly. If dates still show as numbers, start with the date basics lesson. Then try the exercise on this page to work out Ana's retirement date.