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

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

how to calculate retirement age using excel dataset-1

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).

Using DATEDIF function to calculate Retirement Age

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

Using DATEDIF function

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

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.

retirement age in years, months and days

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

retirement age in years, months and days

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.

using vba code

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

using vba code

Press F5 to run the code.

This is the output.

using vba code

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.

how to calculate retirement date in excel

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.

Using arithmetic formula