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.


Download Practice Workbook

You can download the practice workbook from the link below.


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.

how to calculate retirement age using excel dataset-1


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

Using DATEDIF function to calculate Retirement Age

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

Using DATEDIF function

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.

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.

retirement age in years, months and days

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

retirement age in years, months and days

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.

using vba code

  • 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

using vba code

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

using vba code

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.

how to calculate retirement date in excel


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.

Using arithmetic formula

  • Now, go to the Home tab and go to Number Format.

using arithmetic formula

  • At this point, select Date > mm/dd/yyyy > Ok. It will convert the format to your desired format.

using arithmetic formula

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.

using arithmetic formula

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

using EDATE function

  • Now, go to the Home tab and go to Number Format.

using EDATE function

  • At this point, select Date > mm/dd/yyyy > Ok. It will convert the format to your desired format.

using EDATE function

  • Finally, drag the Fill handle for the rest of the cell and you will get your desired output.

using EDATE function

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
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo