Formula to color RYG balls based on date/deadline


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!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!