Changing status symbols if a task is not completed before due date
Hello,
I recently found someone with a similar issue after doing a search in the Community and hoped it would help me but I haven't been able to succeed even though it got me close.
Forum for reference: https://community.smartsheet.com/discussion/59686/formula-to-change-status-symbol-if-a-task-is-not-completed-before-due-date
I need to do the following:
- If project has not started = "Grey"
- If project is in progress (percentage is increasing 1%-99% to show in progress) = Green
- If project reaches 100% at or before end date = "Green"
- If project is at 75% or below and we're 2 days away from the end date, project is not complete = "Yellow"
- If project/task is at end date and the percentage complete is not at 100% = "Red" (I guess based on the bullet above, 76% and up = "Red")
Like the person who had the same issue above, I have "Start Date", "End Date", "% Complete" columns.
I hope this is clear. Let me know if you have any questions.
Thanks,
Judy
Best Answer
-
Try something like this...
=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF(AND([% Complete]@row<= 0.75, [End Date]@row <= TODAY(2)), "Yellow", IF([% Complete]@row = 0, "Grey", "Green"))))
Answers
-
Try something like this...
=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF(AND([% Complete]@row<= 0.75, [End Date]@row <= TODAY(2)), "Yellow", IF([% Complete]@row = 0, "Grey", "Green"))))
-
Hi @Paul Newcome ,
It appears to be working for all scenarios but "Grey". When I change a % Complete to 0%, there is no status symbol, instead it's blank. When I was playing around with your original formula from the last forum, I had: =IF(AND([% Complete]@row = 0, [Start Date]@row <= TODAY()), "Gray"... and that worked but not sure how to apply it here.
Thanks for your help.
-
Hi Paul,
I found the error! It was just the spelling of "Grey", it needed to be "Gray". Thanks again for all your help.
-
Sorry about that. I always mix that one up even in my own sheets. You'd think that I would remember it by now.
Glad you were able to figure it out and get it working.
-
I do the same and that's how I was able to figure it out hahaha. Thanks again and have a great weekend!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!