Auto RAG based on milestones
I'm looking to write a formula that auto calculates a RAG on a row and struggling where to start as not my area of expertise. I've looked in the community and examples provided have not help me answer my question so hoping someone can help.
My logic is if the Planned Start Date is in the past/overdue then it should flag RED in my RAG column. If the dates are within the right time frame then Green and possibly if a week to go (happy with input for suggested date logic) then for it to flag YELLOW as a warning it is near.
That's my idea chucked on paper but again happy for anyone to suggest a diiferent way to calculate.
Many thanks in advance.
Amanda
Best Answer
-
Ok. Lets try this then:
=IF(OR(Status@row = "Complete", [Planned Start Date]@row > TODAY(7)), "Green", IF([Planned Start Date]@row >= TODAY(), "Yellow", "Red"))
Answers
-
How would you want to account for completion of a task? Based on your above logic, eventually all tasks would show red.
-
If a task is completed then I'd like it to appear GREEN as well to reflect all done.
-
Ok. Lets try this then:
=IF(OR(Status@row = "Complete", [Planned Start Date]@row > TODAY(7)), "Green", IF([Planned Start Date]@row >= TODAY(), "Yellow", "Red"))
-
Thank you so much Paul, that is working exactly how I imagined it to work 🙂
-
Hi Paul and rest of the community.
The below formula works absolutely fine and has really helped with our reporting of projects. What I did forget about is we have an additional dropdown option called "Complete with evidence" which also needs to return a GREEN RAG. I've tried writing this additonal requirement into the formula and now keep breaking it 🙄 so returning back here for help please.
=IF(OR(Status@row = "Complete", [Planned Start Date]@row > TODAY(7)), "Green", IF([Planned Start Date]@row >= TODAY(), "Yellow", "Red"))
After this, I need to attend some form of training for writing formulas. If anyone has any recommendations of possible YouTube videos that are nice and simple then I'd be most grateful as I will be doing this more so in my job role going forward and would like to develop.
-
Give this a go:
=IF(OR(Status@row = "Complete with evidence", Status@row = "Complete", [Planned Start Date]@row > TODAY(7)), "Green", IF([Planned Start Date]@row >= TODAY(), "Yellow", "Red"))
I am not sure about videos and whatnot, but you can find quite a few help articles if you search for "Formula" here in the Community and then filter the results to show help articles. There is also a link to a "Formula Handbook" on the right of this page near the top and below the quick links.
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
- 143 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!
