RYG Stoplight Formula Looking at Dates, Status and % Complete

Sandra Guzman

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

Paul Newcome

Try something like this...

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

Paul Newcome

Removing the closing parenthesis is the first part. The second part would be updating your quotes around “Not Relevant”. See how they are slanted to show open quote and closed quote? These are called "smart quotes", and Smartsheet cannot accept them. Just delete them and then retype in Smartsheet, and you should be good to go.

Paul Newcome

Try something like this...

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

Jason Wirl

@Paul Newcome

Hi again. Thanks for the 3rd party recommendations earlier. My colleague is checking them out.

While you are at it helping us in the Community with RYG's, can you have a look at mine? I haven't gotten to the nesting part yet because my AND's aren't testing out. The layman's terms are in bold; my attempt at the formula is below.

Columns to evaluate if it's isn't obvious:

• Status (Choices: Complete, In Progress, Not Started)
• Target Start - date column - evaluate if in future
• Target Finish - date column - evaluate if in past or in future

GREEN: Status = Complete OR Status = In Progress AND Target Finish is in Future

=IF(OR(Status@row = "Complete", IF(AND(Status = "In Progress", [Target Finish]:[Target Finish] @cell > TODAY())), "Green")

 

YELLOW: In Progress AND Target Finish = Past Due

=IF(AND(Status = "In Progress", [Target Finish]:[Target Finish] @cell <= TODAY()), "Yellow"

RED: Status = Not Started AND Target Finish = Past Due

=IF(AND(Status = "Not Started", [Target Finish]:[Target Finish] @cell <= TODAY()), "Red"

 

Grey: Status = Not Started AND Target Start is in Future

=IF(AND(Status = "Not Started", [Target Start]:[Target Start] @cell > TODAY()), “Gray”

Paul Newcome

@Jason Wirl Happy to help. Give this one a shot and let me know how it works out for you...

=IF(Status@row = "Complete", "Green", IF(Status@row = "Not Started", IF([Target Start]@row > TODAY(), "Gray", IF([Target Finish]@row <= TODAY(), "Red")), IF(Status@row = "In Progress", IF([Target Finish]@row> TODAY(), "Green", "Yellow"))))

Jason Wirl

Thank you!!! Works great. Can you tell me a little bit more how you got rid of the "AND's" that I had? I would have thought they were necessary in this one. Is it because you nested the date evaluations inside of the Status evaluation for "Not Started"? So we don't have to repeat that argument because the ones that pertain to it are sort-of "grouped" there?

Jason

Paul Newcome

@Jason Wirl That is exactly correct.

=IF(AND(statement1 = 1, statement2 = 1), 1, IF(AND(statement1 = 1, Statement2 = 0), 0))

We repeated "statement1 = 1" in both of the AND statements. To save a few keystrokes it can be rewritten as

=IF(statement1 = 1, IF(statement2 = 1, 1, 0))

If we wanted to include an output for if statement1 <> 1, we would have to include another IF statement when using the first option (there are a few different ways to incorporate, but for this example I will just drop it in the beginning).

=IF(statement1 <> 1, 0, IF(AND(statement1 = 1, statement2 = 1), 1, IF(AND(statement1 = 1, Statement2 = 0), 0)))

Using the second option, we just need to put the output in the "else" portion of the first IF.

=IF(statement1 = 1, IF(statement2 = 1, 1, 0), 0)

Nesting IF's like this can be a little tricky at first, but once you get the hang of it you can really shorten/simplify a lot.

Sandra Guzman

Hi @Paul Newcome,

Thank you so much for your input. It definitely helped to point me in the right direction. I apologize for the late reply.

I need to add one more item to account for the last scenario that we may encounter and seem to be doing something wrong. I have checked and checked everything again but I still get an unparseable message.

What I need to add to the is when a status is not relevant, the RYG field should be left blank. I added this to your suggested formula listed above:

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

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

Your formula is working but when I added the above formula I can't figure out what I am doing wrong. I tried a variety of different things to try and fix this.

Hoping you can help me figure this out. I am sure it's probably something glaring right at me.

Paul Newcome

Removing the closing parenthesis is the first part. The second part would be updating your quotes around “Not Relevant”. See how they are slanted to show open quote and closed quote? These are called "smart quotes", and Smartsheet cannot accept them. Just delete them and then retype in Smartsheet, and you should be good to go.

Sandra Guzman

Hi @Paul Newcome,

Thank you for additional guidance. I made the proposed changes and didn't notice the "smart quotes" - so thank you very much for pointing that out!

After I made the changes I still received an unparseable message. So I started to break the formula down and tested just the first part to ensure that there wasn't any issue with the changes. It worked!

=IF(Status@row = "Not Relevant", " ", IF([% Complete]@row = 1, IF(Status@row = "Complete", "Blue", "Green")))

I added the rest of the formula back and still received the unparseable message so I worked through all the quotes, commas, header columns and parenthises again. And I found the extra parantheses that was causing the issue! So I corrected the closing parantheses after today in the portion of the formula copied below.

AND([% Complete]@row < =.5, [End Date]@row <= TODAY())), "Red"

My final working formula is as follows (figured I would share this since someone else may find this helpful):

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

Thank you so much for your help with this! I feel like I just won the lottery. 



Paul Newcome

Yes. Those parenthesis can get to being a pain sometimes. Haha. Glad you were able to figure it out.