Date of promotion on seniority and retirement age

[Hmingmawii]

I have been trying to calculate date of promotion of employees based on seniority using excel. The variables are as below:
1. The seniority of the employees in the company is based on their date of joining the company. If a person aged 20 joins the company before another person aged 30, the person aged 20 is still the senior in the company even if he is younger in age.
2. The employees work in the company till the last day of the month of their attaining the age of 60.
3. The seniority list of employees based on their date of joining is available.
4. The date of birth and date of retirement of the employees are available.
5. When an employee retires from the company on attaining the age of 60, the person next to him is automatically moves up in the seniority list.
6. There are 5 ranks for promotion for the employees. Each rank can have limited number of persons. For example. the highest rank can have only 2 persons, the second highest rank can have only 4 persons.
I would greatly appreciate if you could assist men in using excel for calculating the date of promotion of the employees for each 5 rank.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.