How to Calculate Retirement Age in Excel (4 Quick Methods)

In many offices around the world, calculating retirement age and retirement date is necessary. Luckily, you can accomplish such types of tasks in Microsoft Excel in bulk and with very quick methods. This article demonstrates how to calculate retirement age and retirement date in Excel.

How to Calculate Retirement Age in Excel: 4 Methods

Suppose you have a dataset of people with their Names, Date of Birth, and Date of Retirement. Now, you want to calculate their retirement age. Here, I will show you 4 methods to do so.

1. Using DATEDIF Function to Calculate Retirement Age

Primarily, you can easily calculate the Retirement Age using the DATEDIF function in Excel. Now, follow the steps below to calculate the Retirement Age.

Steps:

First, add a new column for Retirement Age.
Second, select cell E5 and insert the following formula.

=DATEDIF(C5,D5,”y”)

Here, cell E5 is the first cell for the column Retirement Age. Also,cell C5 and D5 are the first cells of the column Date of Birth and Date of Retirement respectively. The syntax of the function used is DATEDIF(start_date,end_date,unit).

At this point, drag the Fill Handle for the rest of the cells of the column.

2. Utilizing Year YEARFRAC Function to Calculate Retirement Age

Besides, you can also use the YEARFRAC function to calculate the Retirement age. Now, you may follow the steps below to do so.

Steps:

At the very beginning, add a column for Retirement Age.
Next, select cell E5 and insert the following formula.

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

Here, the ROUNDDOWN function is used to round down the value. The arguments of this function are number and num_digits respectively. On the other hand, the arguments of the YEARFRAC function are start_date, end_date, and basis respectively.

$Using yearfrac function in excel$

Finally, drag the Fill Handle for the remaining cells.

$Using yearfrac function in excel$

3. Calculating Retirement Age in Years, Months, and Days

If you want to calculate the retirement age in years, months and days then you can follow the steps below.

Steps:

First, add a column for Retirement Age.
Next, select cell E5 and insert the following formula.

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

⧬ Formula Explanation:

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 following the months.

Lastly, drag the Fill Handle and you will get your result.

4. Applying VBA Code to Calculate Retirement Age in Excel

Another way to calculate the retirement age in Excel is to use VBA code. You can follow the steps below to calculate the retirement age in this method.

Steps:

First, press ALT + F11 to open the VBA.
Now, select Sheet 5 and right-click on it.
Next, sequentially select Insert > Module.

After that, copy the following code and paste it into the blank space.

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

Now, press F5 to run the code.
Finally, the output will be like the screenshot below.

Calculate Retirement Date from Retirement Age in Excel

Let’s assume another dataset where Retirement Age is given along with Name and Date of Birth. Now, you want to calculate their Retirement Dates in mm/dd/yyyy format. Here, I will show you two ways to do so.

1. Inserting Arithmetic Formula to Calculate Retirement Date

You can easily do this by inserting a formula manually. At this point, follow the steps below to calculate the Retirement Date.

Steps:

First, add a column for the Retirement Date.
Next, insert the following formula in cell E5.

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

⬰ Notes:

Cell E5 indicates the first cell of the column Date of retirement.
Also, C5 and D5 cells indicate the first cell of the Date of birth and Retirement Age respectively.
Using this formula, you can calculate a person’s retirement date on the last day of the month of his retirement age.