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

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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"))))

All comments

Paul Newcome

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"))))

Judy Jefferson

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.

Judy Jefferson

Hi Paul,

I found the error! It was just the spelling of "Grey", it needed to be "Gray". Thanks again for all your help.

Paul Newcome

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.

Judy Jefferson

I do the same and that's how I was able to figure it out hahaha. Thanks again and have a great weekend!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.