Calculate retirement age which is date of birth plus sixty years

[uttamsaxena]

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.

 

[mvptomlinson]

Try using:

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

 

[uttamsaxena]

Hi mvptomlinson,
Thanks I will try it.

 

[mvptomlinson]

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?

 

[uttamsaxena]

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