Program Health Formula
Hi all,
I am looking for a new health formula for my action item register. Ideally, it would have the following characteristics:
Symbol would be green if "Due Date" column had a date that was greater than or equal to 30 days away OR if "Completed" column was checked
Symbol would be yellow if "Due Date" column had a date that was greater than or equal to 15 days away but less than 30 days away and "Completed" column was not checked
Symbol would be red if "Due Date" column had a date that was less than 15 days away but and "Completed" column was not checked
Thanks in advance,
Stephen
Answers
-
Hi @Stephen Braunewell - We do something similar in our project plan template to indicate when something is due. Here's our formula:
IF(OR(Status@row = "Complete", Status@row = "Canceled"), "Closed", IF(ISBLANK([Due Date]@row), "Need Date", IF([Due Date]@row < TODAY(), "Overdue", IF([Due Date]@row < TODAY(7), "This Week", IF([Due Date]@row < TODAY(14), "Next Week", "Future")))))), "")
Here's how apply conditional formatting:
You could do something similar with a symbol type column, where the result of your if statement is R, Y, or G, you get one of these:
Hope that's helpful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!