RGB Ball status change based on date information

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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!