I have to create a RAG status based upon the start date given in the file and by looking how many weeks in the process the task takes to get an answer as to whether the start date is feasible.
The time frames to compare the tasks are as follows:
For example if I have a start date of a vacant position as 20th Oct and is in the Advertising stage, the process could take a further 2 weeks for advertising (at worst) or 1 week at best PLUS there would be another 4 weeks for the assessment process and 5 weeks for the offer to be made. Therefore, at worst would take (2+4+5 = 11 weeks at worst) or (1+4+1 = 6 weeks at best). Therefore a start date of 20th Oct is going to give a RAG status of Red.
If the start date was in 12 weeks time, yes it would be 11 weeks at worst but there is a potential to claw back one week from the process to give an amber rating.
Green would be when the start state is for example 12 weeks time.
If on hod, then the RAG status would be green.
i would need a formula to capture all the 4 activities based on the number of weeks it could take and compare this to the start date as to whether this is realistic.
PLEASE HELP.
The time frames to compare the tasks are as follows:
| Activity | Weeks (from) | Weeks (To) |
| Planning | 1 | 2 |
| Advertising | 3 | 5 |
| Assessment | 6 | 10 |
| Offered | 11 | 16 |
For example if I have a start date of a vacant position as 20th Oct and is in the Advertising stage, the process could take a further 2 weeks for advertising (at worst) or 1 week at best PLUS there would be another 4 weeks for the assessment process and 5 weeks for the offer to be made. Therefore, at worst would take (2+4+5 = 11 weeks at worst) or (1+4+1 = 6 weeks at best). Therefore a start date of 20th Oct is going to give a RAG status of Red.
If the start date was in 12 weeks time, yes it would be 11 weeks at worst but there is a potential to claw back one week from the process to give an amber rating.
Green would be when the start state is for example 12 weeks time.
If on hod, then the RAG status would be green.
i would need a formula to capture all the 4 activities based on the number of weeks it could take and compare this to the start date as to whether this is realistic.
PLEASE HELP.
Last edited: