Hello,
I need some help in creating a formula or a macro that will prioritize (and assign a number to) a list of tasks based on two dates. The data is applications received, we have two dates, one is the date the application was received, and the other is the requested date of retirement. I want to be able to prioritize on a first in and first out basis, but accounting for applications we received late which have a closer selected retirement date.
Below is a sample of three employees, which I have manually prioritized. The logic being, Employee 1 gets priority 1 because it is one of the older applications we've received, and the date of retirement is in the past, Employee 2 get priority 2 because it is the next closest retirement date, but not the oldest application, and finally Employee 3 gets ranked third because its the furthest date away, but the oldest application for that date.
I'm not experienced with Excel enough to develop something (either formula or macro) on my own, but having something to assist with this process would be a huge time saver.
Thank you in advance for your assistance.
I need some help in creating a formula or a macro that will prioritize (and assign a number to) a list of tasks based on two dates. The data is applications received, we have two dates, one is the date the application was received, and the other is the requested date of retirement. I want to be able to prioritize on a first in and first out basis, but accounting for applications we received late which have a closer selected retirement date.
Below is a sample of three employees, which I have manually prioritized. The logic being, Employee 1 gets priority 1 because it is one of the older applications we've received, and the date of retirement is in the past, Employee 2 get priority 2 because it is the next closest retirement date, but not the oldest application, and finally Employee 3 gets ranked third because its the furthest date away, but the oldest application for that date.
Employee Name | Retirement Date | Application Received | Todays Date | Days Since Application Received | Priority |
Employee 1 | 2/1/2021 | 2/18/2021 | 3/24/2021 | 34 | 1 |
Employee 2 | 6/1/2021 | 2/22/2021 | 3/24/2021 | 30 | 2 |
Employee 3 | 7/1/2021 | 2/18/2021 | 3/24/2021 | 34 | 3 |
I'm not experienced with Excel enough to develop something (either formula or macro) on my own, but having something to assist with this process would be a huge time saver.
Thank you in advance for your assistance.