# Auto RAG based on milestones

Options
✭✭✭✭

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.

Amanda

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!