Formula to color RYG balls based on date/deadline

Kelly Buse

Hello,

I'm trying to alter the color of our RYG status balls based on the proximity of the date. For example:

• Yellow - if today's date is within 5 days prior and 5 days after the deadline date noted
• Red - if today's date is 5 days + after the deadline date
• Green - if today's date is before the 6 days + before the deadline date

I have devised the following formula but this doesn't work at all and displays the status ball in yellow no matter what the date is changed to!:

=IF(Australia6 - TODAY() < 5, "Yellow", IF(Australia6 - TODAY() > 5, "Yellow", IF(Australia6 - TODAY() < 6, "Green", IF(Australia6 - TODAY() > 6, "Red"))))

I'm quite new to this so your help is greatly appreciated!

Thank you!

L_123

=IF(Australia6 - TODAY() < -5, "Red", IF(Australia6 - TODAY() < 5, "Yellow", "Green"

Give that a try.

L_123

=IF(Australia6 - TODAY() < -5, "Red", IF(Australia6 - TODAY() < 5, "Yellow", "Green"

Give that a try.

Kelly Buse

Works perfectly! Thanks so much for your help.

Natalie Marsh

Hi There

I am trying this exact same formula, and rather than change the color of the bubble, I am seeing the word "red" populate when one of the conditions is met. What am I doing wrong?

L_123

Did you set your column type to ryg?

Natalie Marsh

Yes my column is set to ryg i just verified.

L_123

It's weird, and I don't think it should matter, but try capitalizing the R in red. That is the only difference I see between it and the other characters.

This might be a smartsheet side error.

Natalie Marsh

OMG :) that worked lol.....thank you

Natalie Marsh

But now i have another help question. I actually don't like the formula I created. I want the bubble to change colors if today's date is 7 days prior to the deadline date I want the bubble to be yellow. If today's date is passed the deadline date, I want it to be red and if today's date is 8 days prior to deadline date or more then green. Make sense. I am trying to reference that "deadline date" cell as my criteria but I am struggling.

Natalie Marsh

I believe I have my final answer so thought I would share. We will see :)

Natalie Marsh

Hi there,

I thought I had this figured out but I did not. Disregard the above.

This is what I would like to do and am struggling to get it working correctly. If the deadline date is within 7 days, I want the bubble to turn yellow. Anything after the deadline date has to be "Red" and anything before the 7 days of deadline date should be "Green". This is what I have and clearly not correct.

Can you help again please :)

Abe B.