RAG Status by Percentage Complete and today's date
I have a formula for RAG status which I've been using to manage performance across multiple projects looking at the columns end date, % complete, and referencing today's date. However it is not working as planned and wanted to ask for some help please.
I would like the formula to simply change the RAG status for a tasks as follows:
Complete or up to 5% behind schedule = green;
5-10% behind schedule = amber;
11% or more behind schedule = red.
The way the formula is currently working is as follows: if the task is ahead of schedule or complete it is green; if the task is behind schedule but the due date hasn't passed it is amber, if it is behind schedule and the due date has passed it is red.
IF(AND(TODAY() > [End Date]@row, [% Complete]@row < 0.9), "Red", IF([% Complete]@row >= ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)), "Green", "Yellow"))))
Any help would be excellent, thank you!
Help Article Resources
Check out the Formula Handbook template!