RAG Status by Percentage Complete and today's date
Hi
Query
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.
Current Status
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.
Current Formula
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!
D
Answers
-
If I understand correctly, you're calculating the % behind or ahead of schedule by counting the number of days since the start date and dividing that by the scheduled number of days in the project, yes? So for instance, if I had a project starting 8/1/22 and ending 8/21/22, and today is 8/22/22, I'm at 1.05, or 5% behind schedule. But if my end date was 8/23/22, today I'd be at .95, or 5% ahead of schedule.
Looking at your desired logic:
Anything 100% complete OR up to 5% behind schedule should be green.
Anything not 100% complete, AND more than 5% but only up to 10% behind schedule, should be yellow.
Anything not fitting the above criteria (i.e. anything not yet complete and over 10% behind schedule,) should be red.
So let's try this:
=IF(OR([% Complete]@row = 1, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) <= 1.05), "Green", IF(AND([% Complete]@row < 1, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) > 1.05, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) <= 1.1), "Yellow", "Red"))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Many thanks for coming back to me Jeff.
Unfortunately, the formula hasn't worked. When testing in one of the projects, it is showing the project, which is 30% behind schedule as green whereas based on the criteria below it should be Red. The logic you outline above is correct, however
I have a baseline column which I compare against, Planned % Complete with a simple formula as follows:
=IF(TODAY() >= [End Date]@row, 1, IF(TODAY() <= [Start Date]@row, 0, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)))
As you will see from the screenshot - the project should be 44% complete, but it is 11% complete and showing as Green with your formula.
Any other suggestions would be super helpful.
The Red / Amber / Green criteria are as follows:
Green
Up to 5% behind schedule vs Planned % Completion OR ahead of schedule OR complete = Green
Amber
Between 90%-95% behind planned % completion = amber
Red
<90% behind planned percentage completion = red
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!