Struggling with Excel Formulas to Calculate Retirement Date – Can You Help?

I am trying to create a sheet on Excel in which I need to calculate the retirement date. The condition is that the employee gets retired if they are either 58 years of age or have worked in the company for 25 years (whichever is earlier). Until now, I have used:

=DATEDIF(D2, TODAY(), "Y") for calculating age, and for calculating work experience:

=IF(OR(F2 >= 58, G2 >= 25), "RETIRED", "YEARS LEFT") for determining whether the employee is retired or how many years are left.

Now, I want to calculate the date for retirement.

WAITING FOR URGENT RESPONSE.

Kindly guide me through.

Regards,

Tapanjyoti

Hi,

Great to see you use the DATEDIF() function. You can do the following:

For 58 years criteria:
Here, A1 contains the employee's date of birth in dd-mmm-yy format.
=DATE(YEAR(A1)+58, MONTH(A1), DAY(A1))

For 25 years criteria, replace 58 in the above formula with 25, and you will get a date 25 years from now.

Regards,
Niilesh

Hope this helps....

Nilesh

This formula works fine, but in my organization, the date of retirement is the last day of the month upon attaining the age of 60 years. Please provide a formula for it.