Date ranges and symbols

I am looking for a formula to automatically change a symbol colour based on contract expiry dates. Here is what I need each symbol to signify about the expiry date column -
Green dot = If the contract expiry date is greater than 365 days from today
Yellow dot = If the contract expiry date is less than 365 days away from today
Blue Dot = if the date has past todays date
Red dot = box not populated
All help will be gratefully received.
It would also be cool to get up a notification when the symbols change colour.
Thanks in advance
Richard
Comments
-
Hi Richard.
Try this formula. IF the name of your column is different from Contract Expiry, please update that column name.
=IF(ISBLANK([Contract Expiry]@row), "Red", IF([Contract Expiry]@row - TODAY() >= 365, "Green", IF(AND([Contract Expiry]@row - TODAY() >= 0, [Contract Expiry]@row - TODAY() < 365), "Yellow", IF([Contract Expiry]@row < TODAY(), "Blue", ""))))
You can set up an alert for whenever the column changes.
-
=IF(ISBLANK([Contract Expiry]@row), "Red", IF([Contract Expiry]@row >= TODAY(365), "Green", IF([Contract Expiry]@row >= TODAY(), "Yellow", "Blue")))
Give this a whirl...
EDIT: Argh!!! Sorry Mike. I need to remember to refresh before replying to threads I opened earlier in the day.
-
-
Thanks, appreciate the help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 515 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!