RYG formula assistance

Christopher Pretty

I have a formula in a RYG column that should change the color based on date and % complete. I think I am missing something. Only red or green will show, i have not successfully made it turn yellow. It will just be blank, red or green.

=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row = TODAY(), IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.75, "Yellow")), IF([End Date]@row = TODAY(), IF([% Complete]@row > 0.75, "Green", IF([% Complete]@row < 0.25, "Red", IF([% Complete]@row < 0.5, "Yellow")))))))

John Jonassen

Hi @Christopher Pretty

A couple of things. I've recently learned that the Today() function like to ALWAYS be on the left side of the Greater Than (>) or the Less Than (<) symbols. So you'll want to make sure to write your formulas in that order when using Today(). When it comes to equals (=), it doesn't matter.

You might want to take a look and re-evaluate the different combinations that you are trying to RYG. Here is what I was able to gather from your existing formula:

Green

OR([% Complete]@row = 1, [Start Date]@row > TODAY

IF([End Date]@row = TODAY(), IF([% Complete]@row > 0.75

Yellow

IF([% Complete]@row < 0.75

IF([% Complete]@row < 0.5

Red

IF([End Date]@row < TODAY(),

IF(AND([End Date]@row = TODAY(), [% Complete]@row < 0.25)

IF([% Complete]@row < 0.25

All that being said, here is an updated formula where I do get Yellow as a result, but not sure if it is at the level of accuracy that you are looking for:

=IF(OR([% Complete]@row = 1, TODAY() < [Start Date1]@row, AND([End Date1]@row = TODAY(), [% Complete]@row > 0.75)), "Green", IF(OR(TODAY() > [End Date1]@row, AND([End Date1]@row = TODAY(), [% Complete]@row < 0.25), [% Complete]@row < 0.25), "Red", "Yellow"))

John Jonassen

Hi @Christopher Pretty

A couple of things. I've recently learned that the Today() function like to ALWAYS be on the left side of the Greater Than (>) or the Less Than (<) symbols. So you'll want to make sure to write your formulas in that order when using Today(). When it comes to equals (=), it doesn't matter.

You might want to take a look and re-evaluate the different combinations that you are trying to RYG. Here is what I was able to gather from your existing formula:

Green

OR([% Complete]@row = 1, [Start Date]@row > TODAY

IF([End Date]@row = TODAY(), IF([% Complete]@row > 0.75

Yellow

IF([% Complete]@row < 0.75

IF([% Complete]@row < 0.5

Red

IF([End Date]@row < TODAY(),

IF(AND([End Date]@row = TODAY(), [% Complete]@row < 0.25)

IF([% Complete]@row < 0.25

All that being said, here is an updated formula where I do get Yellow as a result, but not sure if it is at the level of accuracy that you are looking for:

=IF(OR([% Complete]@row = 1, TODAY() < [Start Date1]@row, AND([End Date1]@row = TODAY(), [% Complete]@row > 0.75)), "Green", IF(OR(TODAY() > [End Date1]@row, AND([End Date1]@row = TODAY(), [% Complete]@row < 0.25), [% Complete]@row < 0.25), "Red", "Yellow"))

Mike Wilday

I also find that sometimes it helps to reorder the iF statements. Try putting the yellow IF statement first in the list. What results do you get then?