RGB Ball status change based on date information

Options
✭✭✭✭

Hello

I need a formula that will change the color of the balls based on the Tentative Project Finish date. Example:

Green: 30 days due from the Tentative Project Finish date

Yellow: 14 days due from the Tentative Project Finish date

Red: 5 days due from the Tentative Project Finish date

If no Tentative Project Finish date is entered, leave it blank.

If the Tentative Project Finish date is 31 days or over, BLUE ball

Thanks

Answers

• ✭✭✭✭✭✭
edited 10/05/23
Options

I am assuming you are looking for the time from Today. Otherwise any other date you want, just replace the TODAY().

Put this code in the RYGB Symbol colum

=IF(ISDATE(TPFD@row), IF(TPFD@row - TODAY() > 30, "Blue", IF(TPFD@row - TODAY() > 14, "Green", IF(TPFD@row - TODAY() > 5, "Yellow", "Red"))), "")

If you add a hidden column Delta

=TPFD@row - TODAY()

Then can simplify the code and reduce calculations

=IF(ISDATE(TPFD@row), IF(Delta@row > 30, "Blue", IF(Delta@row > 14, "Green", IF(Delta@row > 5, "Yellow", "Red"))), "")

Use one or the other, and call the columns whatever you like.

You can also convert them to column formulas.

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!