Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

## 4 Methods to Calculate Retirement Age in Excel

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. Read More: How to Calculate Age from Birthday in Excel (8 Easy Methods)

### 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. • Finally, drag the Fill handle for the remaining cells. ### 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. Read More: How to Calculate Age in Excel in Years and Months (5 Easy Ways)

### 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. Read More: Excel VBA: Calculate Age from Date of Birth

## 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. • Now, go to the Home tab and go to Number Format. • At this point, select Date > mm/dd/yyyy > Ok. It will convert the format to your desired format. Note: Also, you can simply press CTRL + 1 to open the Format Cells dialog box.

• Finally, after you have your required format, drag the Fill Handle for the rest of the column. Read More: Excel formula to Calculate Age on a Specific Date

### 2. Using EDATE Function to Calculate Retirement Date

You can also use the EDATE function to calculate the Retirement Date. Now, follow the steps below to do so.

Steps:

• First, add a column for the Date of Retirement.
• After that, select cell E5 and insert the following formula.
=EDATE(C5,12*D5)

Here, the syntax of the EDATE function is EDATE(start_date,months). • Now, go to the Home tab and go to Number Format. • At this point, select Date > mm/dd/yyyy > Ok. It will convert the format to your desired format. • Finally, drag the Fill handle for the rest of the cell and you will get your desired output. Read More: How to Calculate Average Age in Excel (7 Easy Methods)

## Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. If you want to read more articles like this, you can visit our website ExcelDemy.

## Related Articles #### Sowmik Chowdhury

Hello! Welcome to my Profile. I , Sowmik Chowdhury, graduated from the Bangladesh University of Engineering and Technology from the Department of Naval Architecture and Marine Engineering. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this..

We will be happy to hear your thoughts 