# Calculate retirement age which is date of birth plus sixty years

This is a discussion on Calculate retirement age which is date of birth plus sixty years within the Excel Questions forums, part of the Question Forums category; I need to calculate retirement age. 1. A per son retires on the last day of the month he completes ...

1. ## Calculate retirement age which is date of birth plus sixty years

I need to calculate retirement age.
1. A per son retires on the last day of the month he completes 60 years. Say if born on 02 july 1967 will retire on 31 July 2027. Person born on 14 Feb 1952 will retire on 29 Feb 2012.

2. Only other condition is that if the person is born on 1st of any month he retires on last day of the previous month. Say if born on 01 july 1967 will retire on 30 June 2027. Person born on 01 Feb 1952 will retire on 31 Jan 2012.

I tried the following formula but stuck at how to get 31, 30, 28, 29 in respective months. If Date of Birth is in cell B1 then formula in other cell:
=IF(DAY(B1)=1,(DATE(YEAR(B1)+60,MONTH(B1)-1,DAY(30))),(DATE(YEAR(B1)+60,MONTH(B1),DAY(30))))

Can you help me in making a formula for this.

2. ## Re: Calculate retirement age which is date of birth plus sixty years

Try using:

=IF(DAY(A1)=1,DATE(YEAR(A1)+60,MONTH(A1),0),DATE(YEAR(A1)+60,MONTH(A1)+1,0))

3. ## Re: Calculate retirement age which is date of birth plus sixty years

Hi mvptomlinson,
Thanks I will try it.

4. ## Re: Calculate retirement age which is date of birth plus sixty years

Which is it? In your first post you said only those born on the FIRST of the month retire on the last day of the previous month, while all others retire on the last day of the month they were born in.

Now you're saying those born on the FIRST and SECOND retire on the last day of the previous month, and those born on the 3-31 retire at the end of that month?

5. ## Re: Calculate retirement age which is date of birth plus sixty years

Hi mvptomlinson,
I am sorry for some wrong calculation, but your formula works perfectly. Thanks a lot for the help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•