How to Calculate the Retirement Age in Excel - 4 Quick Methods

The dataset showcases Names, Date of Birth, and Date of Retirement.

Method 1. Using the DATEDIF Function to Calculate the Retirement Age

Steps:

Add a new column: Retirement Age.
Select E5 and enter the following formula.

=DATEDIF(C5,D5,”y”)

E5 is the first cell in the column Retirement Age. C5 and D5 are the first cells in the columns Date of Birth and Date of Retirement. The syntax of the function is: DATEDIF(start_date,end_date,unit).

Drag down the Fill Handle to see the result in the rest of the cells.

Method 2 – Utilizing the Year YEARFRAC Function to Calculate the Retirement Age

Use the YEARFRAC function.

Steps:

Add a new column: Retirement Age.
Select E5 and enter the following formula.

=ROUNDDOWN(YEARFRAC(C5,D5,1),0)

The ROUNDDOWN function rounds down the value. The arguments are number and num_digits. The arguments of the YEARFRAC function are start_date, end_date, and basis.

$Using yearfrac function in excel$

Drag down the Fill Handle to see the result in the rest of the cells.

$Using yearfrac function in excel$

Method 3 – Calculating the Retirement Age in Years, Months, and Days

Steps:

Add a new column: Retirement Age.
Select E5 and enter the following formula.

=DATEDIF(C5,D5,”Y”)&” Years, “&DATEDIF(C5,D5,”YM”)&” Months, “&DATEDIF(C5,D5,”MD”)&” Days”

Formula Breakdown

DATEDIF(C5,D5,”Y”)&” Years will give the retirement age in years.
“&DATEDIF(C5,D5,”YM”)&” Months will add the months after the years.
Finally, “&DATEDIF(C5,D5,”MD”)&” Days” will add the days after the months.

Drag down the Fill Handle to see the result in the rest of the cells.

Method 4 – Applying a VBA Code to Calculate the Retirement Age in Excel

Steps:

Press ALT + F11 to open the VBA.
Select Sheet 5 and right-click it.
Select Insert > Module.

Enter the following code.

Sub Calculate_RetirementAge()
Range("E5").Formula = "=DATEDIF(C5,D5,""y"")"
Range("E6").Formula = "=DATEDIF(C6,D6,""y"")"
Range("E7").Formula = "=DATEDIF(C7,D7,""y"")"
Range("E8").Formula = "=DATEDIF(C8,D8,""y"")"
Range("E9").Formula = "=DATEDIF(C9,D9,""y"")"
Range("E10").Formula = "=DATEDIF(C10,D10,""y"")"
Range("E11").Formula = "=DATEDIF(C11,D11,""y"")"
Range("E12").Formula = "=DATEDIF(C12,D12,""y"")"
Range("E13").Formula = "=DATEDIF(C13,D13,""y"")"
Range("E14").Formula = "=DATEDIF(C14,D14,""y"")"
End Sub

Press F5 to run the code.

This is the output.

Calculate the Retirement Date based on the Retirement Age in Excel

Retirement Age is given along with Name and Date of Birth. You want to calculate the Retirement Dates in mm/dd/yyyy format.

Method 1- Using an Arithmetic Formula to Calculate the Retirement Date

Steps:

Add a new column: Date of Retirement.
Select E5 and enter the following formula.

=IF(DAY(C5)=1,DATE(YEAR(C5)+D5,MONTH(C5),0),DATE(YEAR(C5)+D5,MONTH(C5)+1,0))

⬰ Notes:

E5 indicates the first cell of Date of retirement.
C5 and D5 indicate the first cells of Date of birth and Retirement Age.
Using this formula, you can calculate the retirement date as the last day of the month of the retirement age.