Update health indicator formula for tasks that are not due to start

Melissa Kosiewicz

Hello,

I'm relatively new to Smartsheet and found the following formula on this discussion board (I can't find original post). I need to update this formula to show "green" for tasks that are not scheduled to start yet, rather than blank.

The columns in play are Planned Start Date, Planned End Date and % Complete 

• If task has NOT reached its Start Date and % Complete = 0, green health ball
• If task has reached its Start Date or not reached its End Date and % Complete > 0, green health ball
• If % Complete = 1, blue health ball
• If task has reached or passed its Start Date and % Complete = 0, yellow health ball
• If task has passed its End Date and % Complete < 1, red health ball

Here is the current formula that I need to modify for bullet 1 above:

=IF([% Complete]@row = 1, "Blue", IF([Planned End Date]@row < TODAY(), "Red", IF([Planned Start Date]@row < TODAY(), IF([% Complete]@row = 0, "Yellow", "Green"), IF([% Complete]@row > 0, "Green"))))

Paul Newcome

Try something like this...

=IF([% Complete]@row = 1, "Blue", IF(TODAY()> [Planned End Date]@row, "Red", IF(OR([% Complete]@row > 0, TODAY() < [Planned Start Date]@row), "Green", "Yellow")))

Melissa Kosiewicz

Thank you Paul, this worked perfectly.

Paul Newcome

Try something like this...

=IF([% Complete]@row = 1, "Blue", IF(TODAY()> [Planned End Date]@row, "Red", IF(OR([% Complete]@row > 0, TODAY() < [Planned Start Date]@row), "Green", "Yellow")))

Melissa Kosiewicz

Thank you Paul, this worked perfectly.

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response as "helpful" so that others searching for a similar solution can know that one may be found here.