Need a formula for % completed to change status health, as well as % completed to update status


Hi there,

I am after two formulas:


I need my % Complete to reflect the Status, example below:

0% equals status - Not Started

1 - 99% equals status - In Progress

100% equals status - Complete


I need my Health status to reflect the following:

Completed equals Green

In Progress equals Yellow

Not Started equals Grey

Items that are In Progress or Not Started and it is past the due date, equals Red. I.e. If its 31/03/2023 I want anything that is in Not Started or In Progress that was due 30/03/2023 (and any other date before) to equal RED.

Please help me.....



  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 03/31/23

    Hi @clairemorse09 ,

    These should do what you're after.

    Formula 1:

    =IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))

    Formula 2:

    =IF(Status@row = "Complete", "Green", IF([Due Date]@row < TODAY(), "Red", IF(Status@row = "Not Started", "Gray", "Yellow")))

    Sample data & output for today (31/03):

    Hope this helps, but if you've any questions etc. then just post! 😊

  • clairemorse09

    Hi Nick,

    Thanks for getting back to me, but the second formula isnt working, any other ideas?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!