Health formula from Status


Hi all. I'm new and trying to tweak a health column formula. We've decided to remove our % Complete column which we'd originally used.

Original Formula:

=IF(AND([Due Date]@row < TODAY(), NOT(ISBLANK([Due Date]@row)), OR([% Complete]@row <> 1, ISBLANK([% Complete]@row))), "Red", IF(AND(NOT(ISBLANK([Due Date]@row)), OR([% Complete]@row = 0, ISBLANK([% Complete]@row))), "Gray", IF(AND(([% Complete]@row < 0.5), [Network Days]@row <= 2, NOT(ISBLANK([Due Date]@row))), "Yellow", IF(NOT(ISBLANK([Due Date]@row)), "Green ", " "))))

I'd like to use a simple logic such as:

Past due date and NOT complete = Red

Not started = Gray

In progress = Green

Blocked = Yellow

Can anyone please recommend a formula that would work?

Thank you!



  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Christine Brown (SAI) ,

    I assumed your comlplete was a checkbox and you previous "yellow" formula was the criteria you're using for Blocked. if not, you'll need to adjust. Try:

    =IF(OR([start date]@row>=today(), ISBLANK([start date]@row)), "Gray", IF(AND([due date]@row<Today(), complete@row=0), "Red", IF(AND([Network Days]@row <= 2, NOT(ISBLANK([Due Date]@row)), "Yellow", "Green")))



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Christine Brown (SAI)
    edited 01/19/21

    Thank you @Mark Cronk ! I'm still getting a syntax error unfortunately. I think we don't want Yellow/Blocked associated with the date, just status change. We currently had a green strikethrough for complete items.

    I will continue to tinker. Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!