RYGB status formula - some issues

ACL

Hoping to get some help! I'm new to Smartsheet and have been piecing together formulas from the community posts but running into an issue. I'm using the following formula to auto format the Schedule Health column with Red, Yellow, Green, Blue balls or just display "future" (the screen shot shows the criteria in column Task Name). I'm struggling with rows 77 and 79, somehow if the End Date is greater than 1 week from today, it will show "Future", instead of the corresponding Yellow or Green ball. I think I need to add one more condition to the formula for Yellow and Green but can't seem to process the info in my head!

= IF([Actual % Complete]@row = 1, "Blue", IF(OR(AND([Actual % Complete]@row <= 0.3, [Start Date]@row < TODAY(), [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), AND([Actual % Complete]@row <> 1, [End Date]@row < TODAY())), "Red", IF(AND([Actual % Complete]@row < 0.75, [Start Date]@row < TODAY(), [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Yellow", IF(AND([Actual % Complete]@row >= 0.75, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Green", "Future"))))

Paul Newcome

Let's try a little bit of clean up to see if that helps first. If not, it should at least help with troubleshooting.

=IF([Actual % Complete]@row = 1, "Blue", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(7), IF([Actual % Complete]@row <= 0.3, "Red", IF([Actual % Complete]@row <= 0.75, "Yellow", "Green")), "Future")))

ACL

Thanks for the quick response Paul! So row 77 and 79 are still displaying "Future".

I think rows 78 and 80 are now only looking at one criteria now vs a combination:

• for example, in row 80, Red is taking into account end date being within 7 days from today but not looking at start date, if start date is in the future, that task should show "Future", if start date has passed then it should be Red)
• Row 78, It shows Yellow but does not consider that the Actual % Complete is greater than 75%. In this case, it should show Green

Maybe I'm complicating things by having too many combinations? I'm open to suggestions on how to simplify the criterias as well.

Paul Newcome

Right. I didn't think that would be the final formula. Just a cleanup to make it easier to manage and tweak. I was just going off of the logic already in your previous formula so we could have somewhere to start. The only times you specified yellow or green in that was when the end date was within the next 7 days.

Are you able to spell out all of your criteria? It looks like there are some conflicting bits, and having it all spelled out will help with the overall formula. Something like...

% Complete = 100 --> "Blue"

Start Date in future --> Blank or "Future"?

End Date in past --> "Red"

% Complete less than 30 --> "Red"

% Complete less than 75 --> "Yellow"

All else "Green"

The pieces I am not sure on is that your initial formula only has R, Y, or G for those where the End Date is within 7 days and highlighted bold as if that was in there intentionally, and in your Task Name column you even specified that "due within 1 week" was part of the criteria. Then when you further describe the problem, it sounds as if you don't care when the End Date is (unless it is in the past) only the percentage. I am also not sure if you want blank or "Future" for rows where the Start Date is in the future.