uttamsaxena
Board Regular
- Joined
- Apr 22, 2003
- Messages
- 182
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.
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.