How to calculate desired retirement date

satya (Executive Excise) (1839 Points)

17 August 2013  

Dear Expertz,

Please give suitable formula to get my desired retirement date;

suppose person's DOB is 01-May-1971, then his retirement age is 58 years, his retirement date is 1-May-1971, actually in our company when his superannuation comes on 01 date, his superannuation date becomes before date i.e., 30-Apr-1971. if his superannuation is on 02-May-1971 means it should be extended to end of the year.

actually I am using DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)) formula to get his superannuation date, then I am using EOMONTH(IF(DAY(C1)=1,DATE(YEAR(C1),MONTH(C1)-1,DAY(C1)),DATE(YEAR(C1),MONTH(C1),DAY(C1))),0) this formula to less one day when his superannuation comes on 01st of the month.

Now you are all requested to give suitable formula to get like this;

person's DOB is 01-May-1971 superannuation comes on 01 date, his superannuation date becomes before date i.e., 30-Apr-2029. if his superannuation is on 02-May-2029 means it should be extended to end of the year. i.e., 31-Dec-2029.