RYG Column based on % Complete and Date

System

This discussion was created from comments split from: Working with Symbol Formulas.

Robert Cordtz

I'm trying to have my RYG column automated based on % Complete and Due Date.

If Due Date is in the future, Green

If Due Date is in the past and % complete = 100%, Green

If Due date is in the past and % Complete is less than 100%, Red

Then to create yellow, I want to look at % complete vs duration and refer to due date. So if the duration is 10 days and the due date is fewer than 5 days away and % complete is less than 50% - I want to return yellow.

How can I phrase this formula?

Genevieve P.

Hi @Robert Cordtz ,

I'll break down each instruction by colour... then we'll order them together:

If Due Date is in the future, Green

or

If Due Date is in the past and % complete = 100%, Green

IF(OR([Due Date]@row > TODAY(), AND([Due Date]@row <= TODAY(), [% Complete]@row = 1)), "Green",

If Due date is in the past and % Complete is less than 100%, Red

IF(AND([Due Date]@row <= TODAY(), [% Complete]@row < 1), "Red",

So if the duration is 10 days and the due date is fewer than 5 days away and % complete is less than 50% - I want to return yellow.

IF(AND([Due Date]@row <= TODAY(5), [% Complete]@row < 0.5, Duration@row = 10), "Yellow"

Now that you have 3 rules, lets make sure the put them in the right order. IF statements read left-to-right, and as soon as a criteria is met it will stop. This means that if we started with the Green rule, it wouldn't take in to account that the Yellow rule also looks into the future a bit. So! Let's order them backwards, starting with the Yellow rule, moving to the red, and finishing with the green.

FULL FORMULA:

=IF(AND([Due Date]@row <= TODAY(5), [% Complete]@row < 0.5, Duration@row = 10), "Yellow", IF(AND([Due Date]@row <= TODAY(), [% Complete]@row < 1), "Red", IF(OR([Due Date]@row > TODAY(), AND([Due Date]@row <= TODAY(), [% Complete]@row = 1)), "Green")))

Let me know if this works for you! Keep in mind there may be gaps in there that we haven't specified a certain colour for, so test it out on all the possible date options you see that might happen to make sure it's outputting the correct colour.

Cheers,

Genevieve