Schedule Health (R, Y, G Symbols) Based off of End Date and % Complete Columns

RFedders

Hello, I had previously posted a question regarding how to get red and green symbols based off of the due date and % Complete columns within a sheet. I now want to configure a formula that includes a yellow symbol if the due date is today -3 days. This means the symbol would be green until 3 days before the date. I would like it to remain red after the due date.

The formula I currently use is =IF(OR([% Complete]@row = 1, TODAY() < [End Date]@row), "Green", "Red") but this will have to change to incorporate the yellow symbol.

Is there a formula I can use to answer this question?

Jeff Reisman

You'll want to nest the Yellow choice as the negative condition in the first IF statement, and make Red the negative condition of the nested Yellow:

=IF(OR([% Complete]@row = 1, TODAY() < [End Date]@row), "Green", IF(OR[% Complete]@row < 1, [End Date]@row >= TODAY(-3)), "Yellow", IF((OR[% Complete]@row < 1, [End Date]@row < TODAY()), "Red")

You also want the Red condition to only be in place if the percent complete is less than 100% and the due date is past.

Jeff Reisman

=IF(OR([% Complete]@row = [% Complete]@row 1, TODAY() < [End Date]@row), "Green", IF(OR([% Complete]@row < 1, [End Date]@row >= TODAY(-3)), "Yellow", IF(OR([% Complete]@row < 1, [End Date]@row < TODAY()), "Red")))

Is this an extraneous [% Complete]@row ?

Jeff Reisman

You'll want to nest the Yellow choice as the negative condition in the first IF statement, and make Red the negative condition of the nested Yellow:

=IF(OR([% Complete]@row = 1, TODAY() < [End Date]@row), "Green", IF(OR[% Complete]@row < 1, [End Date]@row >= TODAY(-3)), "Yellow", IF((OR[% Complete]@row < 1, [End Date]@row < TODAY()), "Red")

You also want the Red condition to only be in place if the percent complete is less than 100% and the due date is past.

RFedders

RFedders

https://community.smartsheet.com/discussion/comment/317290#Comment_317290

Jeff,

Thanks for taking a crack at this formula, I appreciate the effort. Unfortunately, it is reading as #UNPARSEABLE. I went and edited the formula to include the correct usage of parenthesis and I am still returning #UNPARSEABLE in the cell.

This is the formula i used: =IF(OR([% Complete]@row = [% Complete]@row 1, TODAY() < [End Date]@row), "Green", IF(OR([% Complete]@row < 1, [End Date]@row >= TODAY(-3)), "Yellow", IF(OR([% Complete]@row < 1, [End Date]@row < TODAY()), "Red")))

Jeff Reisman

=IF(OR([% Complete]@row = [% Complete]@row 1, TODAY() < [End Date]@row), "Green", IF(OR([% Complete]@row < 1, [End Date]@row >= TODAY(-3)), "Yellow", IF(OR([% Complete]@row < 1, [End Date]@row < TODAY()), "Red")))

Is this an extraneous [% Complete]@row ?