# RAG Status

edited 05/09/24

Hello! Newbie here :)

I was wondering if someone could help with a formula I'm attempting to put together to calculate a RAG symbol status for individual tasks as well as the overall project plan.

If status is complete = blue
If status is in progress OR not started AND end date is today = yellow
If status is in progress OR not started AND end date is in the past = red
If status is not started AND start date is in the past = red
If status is in progress AND end date is in the future = green
=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "In Progress", [End Date]@row <= TODAY()), "Yellow", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row >= TODAY()), "Green", ""))))

Overall Project: calculates the overall status of the project based on the above.

• ✭✭✭✭✭✭

Try this:

=IF(Status@row = "Complete", "Blue", IF(OR([End Date]@row < TODAY(), AND(Status@row = "Not Started", [Start Date]@row < TODAY())), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green")))

• ✭✭✭✭✭✭

Try this:

=IF(Status@row = "Complete", "Blue", IF(OR([End Date]@row < TODAY(), AND(Status@row = "Not Started", [Start Date]@row < TODAY())), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green")))

• Hi @Paul Newcome , thank you for responding! Figured it out in the end, with a few amendments:

=`IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "In Progress", [End Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Green", ""))))))`

Have a great weekend!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!