Calculate retirement date

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:17 PM GMT

Question

I want your help to calculate the retirement date. The rule is that the retirement will be effected at the age of 60 years or 33 years of service whichever is less.

Soppose the date of birth in B2 is 15/04/2000 and date of joining service in C2 is on 22/09/2020

Then, in D2 i want "21/09/2053 by service length"

But if the date of joining service in C2 is on 22/09/2030

Then in D2 i want "14/04/2060 by Age"

Please note that retirement will be at the age of 60 years or 33 years of service from the date of joining whichever is less.

Answer

Hello Nabam,

this formula does what you describe: =IF(DATE(YEAR(B2)+60,MONTH(B2),DAY(B2))<DATE(YEAR(C2)+33,MONTH(C2),DAY(C2)),TEXT(DATE(YEAR(B2)+60,MONTH(B2),DAY(B2))-1,"dd/mm/yyyy")&" by Age",TEXT(DATE(YEAR(C2)+33,MONTH(C2),DAY(C2))-1,"dd/mm/yyyy")&" by service length")

Advertisement

©2014 eLuminary LLC. All rights reserved.