Auto RAG based on milestones

Amanda W _ MSE

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

Paul Newcome

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"))

Paul Newcome

How would you want to account for completion of a task? Based on your above logic, eventually all tasks would show red.

Amanda W _ MSE

https://community.smartsheet.com/discussion/comment/445987#Comment_445987

If a task is completed then I'd like it to appear GREEN as well to reflect all done.

Paul Newcome

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"))

Amanda W _ MSE

https://community.smartsheet.com/discussion/comment/445997#Comment_445997

Thank you so much Paul, that is working exactly how I imagined it to work 🙂

Amanda W _ MSE

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.

Paul Newcome

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.