RYG Status Formula Based on % Complete and Start Date

Hi All,

I'm a novice user of Smartsheet. I'm trying to add a formula based on % Complete and the Start Date. I'd like to automate the status based on the following parameters:

If % Complete is greater than .24 = Green

If % Complete is greater than 0.14 = Yellow

If % Complete is less than 0.14 = Red

If Start Date is in the future = blank

Any suggestions?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:

    =IF([Start Date]@row<= TODAY(), IF([% Complete]@row< 0.14, "Red", IF([% Complete]@row< 0.24, "Yellow", "Green")))

  • Hi Paul,

    Thanks for the quick response. It didn't quite work.

    It did make the future dates blank, but the tasks completed also turned red when converted to a column formula.

    Do you have any recommendations?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How exactly is the % Complete column being populated? Is it a text/number column formatted to show percentages when you type in a number, or do you have to manually type in the % every time?

  • Hi Paul,

    I tried the formula again, and it worked this time!

    If I wanted to make changes to the parameters (what's considered complete, etc.), could you help me with that formula?

    If % Complete is 100% = Green

    If % Complete is greater than 0= Yellow

    If % Complete is 0 = Red

    If Start Date is in the future = blank

    Thanks for all your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!