Need help with formulas using health status and dates
Hi,
I'm trying to do a formula in the "Health" column. Once we are within 30 days from the due date, the status turns "Yellow" and once it is within 7 days of the due date and past it, the status would be "Red". Any other status would be green. I'm at a lost to where even to begin even after viewing some of the videos. If you've seen this before or have experience can you point me in the right direction?
Thanks,
Allison
Answers
-
Hi Allison,
I made an assumption that your sheet also had a column (which I called Status) that would indicate if the task was Complete. Make sure you update this formula to your actual column name and edit my word 'Completed' to your actual wording. Note the wording is case sensitive.
Add this formula to your Health column. The ball will turn green if the Status is Complete, or if the Due Date is 30 d or more greater than Today. The ball will turn Red if the Due Date is within 7 d or less than today. Anything in between will turn to yellow.
=IF(OR(Status@row = "Completed", [Due Date]@row - TODAY() >= 30), "Green", IF([Due Date]@row - TODAY() <= 7, "Red", "Yellow"))
I hope this helps
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 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!