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
*R**etirement 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
*R**etirement 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****H****andle**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
*R**etirement 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****H****andle**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.

- 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: **Metabolic Age Calculator in Excel

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

**Download Practice Workbook**

You can download the practice workbook from the link below.

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

## Related Articles

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