Project Health (Risk) Symbols Formula
Hello,
I am using the project status and end date to determine my project health, but I cant seem to figure out what I need to clear up three issues. I would like to return a yellow in the health column when there is no end date and the project status is either not started or in progress.
I would like to return a blank value in the health column when there is no project status and end date.
I would like to return a red value in the health column if the current date is past the end date and there is no project status.
My current formula is:
=IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(Status@row = "Complete", "Green", "Yellow")))
Thanks in advance!
Sheri
Best Answer
-
Hi @Sheri.C
I believe this will accomplish what you're after:
=IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), ISBLANK([End Date]@row)), "Yellow", IF(Status@row = "Complete", "Green", IF((TODAY() - [End Date]@row > 0), "Red", "")))
If the end date is only filled in when the project is completed, this should be fine. If it is a projected end date then the formula may need an addition to account for projects being "In Progress" with a date in the End Date field - currently they will come back blank.
Hopefully this helps, let us know if you need anything extra adding on!
Answers
-
Hi @Sheri.C
I believe this will accomplish what you're after:
=IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), ISBLANK([End Date]@row)), "Yellow", IF(Status@row = "Complete", "Green", IF((TODAY() - [End Date]@row > 0), "Red", "")))
If the end date is only filled in when the project is completed, this should be fine. If it is a projected end date then the formula may need an addition to account for projects being "In Progress" with a date in the End Date field - currently they will come back blank.
Hopefully this helps, let us know if you need anything extra adding on!
-
Thank you @Nick Korna! It worked :)
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!