Calculating the date of Retirement along with length of Service.

[kingshuk963]

I want to calculate the retirement age (i.e. 58 yrs) from the reference to date of birth with maintain official procedure eg.

if a A's DOB is (column-A) 02 November 1973 then his DOR would be (column-B) 30 November 2031 but in case of a B's DOB is 01 November 1973 then his DOR would be 31 October 2031 one day earlier for the person whose birthday is 1st day of month. Another thing is that from the date of appointment (column-C) his current status of length of service (column-D) should be calculated.


Pls. Chandoo help me to work out in details.

 

[Mike86]

I'm not sure why being born on 02Nov73 causes the DOR to be 1 month longer than the person born on 01Nov73. It looks like you want them retired at the end of the month they were born in?


There's 12 months in a year. You want 58 years of age, so column B would be:


=EOMonth(a1,12*58)


Current length of service in years would be:


=(today()-c1)/365.2

 

[NARAYANK991]

Hi Kingshuk ,


The following formula should do the job , though it may be simplified.


=IF(DAY(A5)>1,EOMONTH(DATE(YEAR(A5)+58,MONTH(A5),DAY(A5)),0),EOMONTH(DATE(YEAR(A5)+58,MONTH(A5),DAY(A5)-1),0))


A5 contains the DoB.


Narayan

 

[NARAYANK991]

Hi Kingshuk ,


Regarding the length of service , do you want the LoS in so many years , months and days , or do you just want it in years ?


Narayan

 

[kingshuk963]

Thanking you both for help me out to calculate the retirement age & length of service. Can we make it more simplified method with same accuracy, pls help me. Cos to make familiar with this expression i had taken a month or more. anyway thnx for the help.


@Narayank991

The length of service is calculating the tenure of the officers was served the office till today as per norms in GOI.