Help needed from #EXCEL, #MACRO, #DB, #Database experts
See this file from DropBox …
This is the Seniority List of Employees.
- In the first Column, We have the employees Unique ID
- In the Second Column, we have the employees Date of Birth
- In the Third Column, we have the Seniority of the Employees as on June 2018
The Promotion for the employees are given based on their seniority
- Assume that Employees in Seniority 1 to 3000 are Called Rank I
- Assume that Employees in Seniority 3001 to 6000 are Called Rank II
- Assume that Employees in Seniority 6001 to 9000 are Called Rank III
- Assume that Employees in Seniority 9001 to 12000 are Called Rank IV
Each Employee Moves up the seniority when some one senior to him retires
Employees Retire on Reaching 58 years subject to the following Rules
- Those who are born from 2 to 31st of a month retire at the end of the month after completing 58 year
- Those are are born on first of a month retire on the last day of previous month
In Other words
- Those who are born from 02-06-1960 to 01-07-1960 will retire on 30-06-2018
- Those who are born from 02-07-1960 to 01-08-1960 will retire on 31-07-2018
- Those who are born from 02-08-1960 to 01-09-1960 will retire on 31-08-2018
- Those who are born from 02-09-1960 to 01-10-1960 will retire on 30-09-2018
- Those who are born from 02-10-1960 to 01-11-1960 will retire on 31-10-2018
- Those who are born from 02-11-1960 to 01-12-1960 will retire on 30-11-2018
- Those who are born from 02-12-1960 to 01-01-1961 will retire on 31-12-2018
So What happens
Assume 40 people in Rank I and 20 People in Rank II (this is assumption. Real Data will be varying) retire on 30-06-2018
- On 01 July 2018
- We need to move those in Seniority 3001 to 3040 (in this case Unique ID 3000 to 3039) from Rank II to Rank I
- Now, In Rank II, 40 people have been promoted to Rank I and Another 20 people have retired
- So, Those in Seniority 6001 to 6060 (in this case Unique ID 6000 to 6059) will be moved from Rank III to Rank II
- And, Those in Seniority 9001 to 9060 will move from Rank IV to Rank III
This has to be done at every Month
In this way
We will have to find a date on which every individual will move to the next higher Rank
- As far as those who are already in Rank I, there is no further promotion
- For those in Rank II, we need to find when each of them will move to Rank I
- For those in Rank III, we need to find when each of them will move to Rank II and then to Rank I
- For those in Rank IV, we need to find when each of them will move to Rank III and then to Rank II and then to Rank I
We need to list against each person
- in Rank II (Seniority 3001 to 6000) when he/she will is get on to Rank I (1 to 3000) ie 1 date
- in Rank III (Seniority 6001 to 9000) When he/she will be reach Rank II (3001-6000) and then Rank I (1 – 3000) ie 2 dates
- in Rank IV (Seniority 9001 to 12000) When he/she will reach Rank III (6001-9000), Rank II (3001-6000) and then to Rank I (1-3000) ie three dates
Feel free to ask doubts
We would be much grateful for the dates
But
If you can also teach how to do, it will be even more helpful