Yet another RYG Balls Question

Hi all,

Can someone either let me know or point in the direction of a post that would assist me in solving for the following.  I am trying to create a formula that would:


  • Return red ball if date in a column titled "Last contacted" contains a date that is 30 days or over.
  • Return a yellow ball if the same column has a date that was 16-29 days.
  • Return a green ball if the same column has a date that is 0-15 days.

I'm sure this has been addressed before, but I'm trying similar solutions which I modify to miserable results.




  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    Hi 66Lambretta,

    I could point you in the direction of loads of similar posts, but I'll save you the trouble. Place the below formula in the first row of Last contacted and then copy down as far the dates go in Last contacted:

    =IF(TODAY() - 30 >= [Last contacted]1, "Red", IF(AND(TODAY() - 29 <= [Last contacted]1, TODAY() - 16 >= [Last contacted]1), "Yellow", IF(AND(TODAY() - 15 <= [Last contacted]1, TODAY() >= [Last contacted]1), "Green", "")))

    Any new rows populated with data will automatically inherit the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!