Project Status Health Formula (Using Due Date, Today's Date, and % Complete)
Hello,
I am new to SmartSheet, and I'm trying to create a formula that shows project health status using the color symbols. These are my parameters below. I've not been able to get any formula to work. I'd greatly appreciate any help with creating this formula.
- PAST DUE - Red: Today’s Date is 1 day after Due Date AND % Complete is < 100%
- AT RISK - Yellow: Today’s Date is 10 before Due Date AND % Complete is < 75%
- ON TRACK - Green: Today’s Date is 10 before Due Date AND % Complete is > 75%
- COMPLETE - Blue: % Complete is 100% regardless of the date.
Thanks!
NC
Answers
-
Try something like this...
=IF([% Complete]@row = 1, "Blue", IF(TODAY() > [Due Date]@row, "Red", IF(TODAY(10) >= [Due Date]@row, IF([% Complete]@row < .75, "Yellow", "Green"))))
-
Just curious if you were able to get this to work. I'd like to do this too.
-
I responded on your other post (here), but this post has different criteria than that one.
Would you be able to clarify what you're looking to do? If you can explain when you want each status ball colour to appear, we can help with the Nested If statement to get you there. It may also be helpful to see a screen capture of your sheet (but block out any sensitive data).
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
This worked for me. Just ensure to select correct column names.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!