Help with a date / Harvey ball formula
Hi - Smartsheet Community,
I'm trying to setup a similar formula but using a date column. I have a column titled Revision Date. I would like to add a column titled Status for the Harvey balls (red, yellow and green) to indicate the following:
If revision date is 365 days older than current calendar date display a red Harvey ball.
If revision date is 275 to 364 days older than current calendar date display a yellow Harvey ball.
If revision date is 1 to 274 days older than current calendar date display a green Harvey ball.
Any help is greatly appreciated. Thank you - Sha
Best Answer
-
Hi Sha,
I believe the following formula should work for the case you described:
=IF([Revision Date]@row <= TODAY(-365), "Red", IF(AND([Revision Date]@row <= TODAY(-275), [Revision Date]@row > TODAY(-365)), "Yellow", IF(AND([Revision Date]@row <= TODAY(), [Revision Date]@row > TODAY(-274)), "Green")))
Hope this helps! Let me know if you have any questions.
Best,
Mike
Answers
-
Hi Sha,
I believe the following formula should work for the case you described:
=IF([Revision Date]@row <= TODAY(-365), "Red", IF(AND([Revision Date]@row <= TODAY(-275), [Revision Date]@row > TODAY(-365)), "Yellow", IF(AND([Revision Date]@row <= TODAY(), [Revision Date]@row > TODAY(-274)), "Green")))
Hope this helps! Let me know if you have any questions.
Best,
Mike
-
Hi Mike,
It worked PERFECTLY! Thank you for the quick reply.
Best wishes - Sha
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!