RYG Status Formula Based on % Complete and Start Date

Lbrady

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

Try this:

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

Lbrady

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

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?

Lbrady

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!