Calculate retirement date

QuestionEdit

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.

AnswerEdit

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")