RYG Stoplight Formula Looking at Dates, Status and % Complete

Hi there SS Community!

So I tried to see if I could find something to help me with my formula for having the RYG stoplights populate based on data captured in both the Status and % Complete columns. I started with a basic formula but I wanted to try to also include the Start and End Dates. Here are my outcomes:

(1) If the Status is “Not Relevant” or blank, return blank field

(2) If the % Complete is 100, return Green

(3) If the % Complete is 100 AND the Status is “Complete”, return Blue

(4) If the Status is “Not Started” AND the Start Date is less than or equal to TODAY, return Red

(5) If the Status is “Not Started” AND the Start Date is greater than TODAY, return blank field

(6) If the Status is blank AND the Start Date is greater than TODAY, return blank field

(6) If the End Date is less than or equal to Today AND the % Complete is less than 50, return Red

(7) If the End Date is greater than or equal to Today AND % Complete is greater than 50, return Yellow


Here are the formulas for my above outcomes:

(1) =IF(OR([Status]@row = “Not Relevant”, [Status]@row = " ")), “ ”

(2) IF(% Complete@row = 100), “Green”

(3) IF(AND([% Complete]@row = 100, [Status]@row = “Completed”)), “Blue”

(4) IF(AND([Status]@row = “Not Started”, [Start Date]@row <= TODAY)), “Red”

(5) IF(AND([Status]@row = “Not Started”, Start Date > TODAY)), “ ”

(6) IF(AND([Status]@row = “ ”, Start Date > TODAY)), “ ”

(7) IF (AND([End Date]@row <= TODAY, [% Complete]@row < 50)), “Red”

(8) IF(AND([End Date]@row <> TODAY, [% Complete]@row > 50)), “Yellow”


When I use this I get #UNPARSEABLE and can't figure out where my mistake is.

=IF(OR([Status]@row = “Not Relevant”, [Status]@row = " ")), “ ”, IF(% Complete@row = 100), “Green”, IF(AND([% Complete]@row = 100, [Status]@row = “Completed”)), “Blue”, IF(AND([Status]@row = “Not Started”, [Start Date]@row <= TODAY)), “Red”, IF(AND([Status]@row = “Not Started”, Start Date > TODAY)), “ ”, IF(AND([Status]@row = “ ”, Start Date > TODAY)), “ ”, IF (AND([End Date]@row <= TODAY, [% Complete]@row < 50)), “Red”, IF(AND([End Date]@row <> TODAY, [% Complete]@row > 50)), “Yellow”))))))))

Thanks!

Sandra Guzman

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!