# 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

Use the DATEDIF function.

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.

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

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

• Go to the Home tab and select Number Format.

• Select Date > mm/dd/yyyy > Ok.

Note: Press CTRL + 1 to open the Format Cells dialog box.

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

Read More: Metabolic Age Calculator in Excel

### Method 2 – Using the EDATE Function to Calculate the Retirement Date

Use the EDATE function.

Steps:

• Add a column: Date of Retirement.
• Select E5 and enter the following formula.
=EDATE(C5,12*D5)

The syntax of the EDATE function is EDATE(start_date,months).

• Go to the Home tab and select Number Format.

• Select Date > mm/dd/yyyy > Ok.
• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Calculate Average Age in Excel

## Related Articles

<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF