NEED HELP: Health Formula based on start date, end date, and %100 complete needed.
Hi Guys,
I'm really struggling with a smartsheet formula.
This is what I'm attempting:
=IF([Plan Start]@row > TODAY(), "Green", IF([Plan Start]@row < TODAY(), "Yellow", IF([Due Date]@row >[Due Date]@row TODAY(), "Red") OR(IF([% Complete]@row = "1", "Blue")))
Essentially I'm trying to make a formula that says: If It's before start day = blank, if start date or after = green, if 1 week before due date = yellow, if due date or after = red, if % Complete column is 100% = blue
Any help is appreciated
Answers
-
Hi Daniel,
I did some testing and this might work:
The formula I used was:
=IF([% Complete]@row = 1, "Blue", IF([Plan Start]@row > TODAY(), "", IF(AND([Due Date]@row <= TODAY(), [Plan Start]@row < TODAY()), "Red", IF(NOT([Due Date]@row > TODAY(+7)), "Yellow", IF([Plan Start]@row <= TODAY(), "Green")))))
The formula is based on what is being asked:
if % Complete column is 100% = blue
If It's before start day = blank,
if due date or after = red,
if 1 week before due date = yellow,
if start date or after = green,
Hope it helps,
Sam
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!