How to calculate Status Indicator using %Complete and Target Finish Date

I am trying to achieve the following:

IF the %Complete equals 0%, then status indicator is Gray.

IF the %Complete equals 100%, OR the %Complete is less than 100% and Today is before the Target Finish Date, then the status indicator equals Green.

IF the %Complete is less than 100% and Today is within 1 week of the Target Finish Date, then the status indicator equals Yellow.

IF the %Complete is less than 100% and Today is the Target Finish Date OR Today is past the Target Finish Date, then the status indicator equals Red.

The formula I began with is as follows, but every time I try to modify to reflect above, I get an error:

=IF([% Complete]@row = 0, "Gray", IF([% Complete]@row = 1, "Green", IF(OR(AND([Target Finish Baseline]@row < TODAY() + 5, [% Complete]@row < 1), [% Complete]@row <= 0.5), "Red", IF([% Complete]@row < 1, "Yellow"))))

I would appreciate any guidance! Thank you!

Find more posts tagged with

Formulas

functionality

Accepted answers

All comments

ChloeSmith

Hi Pamela,

I'm not sure if this is perfect, but it seemed to work when I was testing it out.

=IF([%Complete]@row = 0, "Gray", IF([%Complete]@row = 100, "Green", IF(AND([%Complete]@row < 100, [Target Finish Baseline]@row > TODAY(+5)), "Green", IF(AND([%Complete]@row < 100, [Target Finish Baseline]@row < TODAY(+6), [Target Finish Baseline]@row > TODAY()), "Yellow", IF(AND([%Complete]@row < 100, [Target Finish Baseline]@row <= TODAY()), "Red")))))

Hope that helps!

Pamela Campo

Hi Chloe! Thank you for your quick response. It seems to work for the most part, except for 100% Complete. These turn red and yellow when the target date is approaching within 1 week or is past due. Do you know how to adjust so that 100% Complete turns green, regardless of the date?

ChloeSmith

@Pamela Campo I'm so sorry! I didn't see this response on here. When I've tested it, anything that has 100% complete has been changing to green regardless of the date (I've tried past, future, and current). Are you able to share a screenshot of what you're seeing and provide the formula that's on there (just in case)?

Mark Proulx

Resurrecting this thread with a screenshot as I get the same results with tasks 100%. Any and all help would be most appreciated.

Mark Proulx

Actually, I figured it out and modified the formula (column headings changed as my sheet was different. I changed the value of '100' to '1'and it worked, although not sure why.

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row = 0, Finish@row <= TODAY()), "Red", IF(AND([% Complete]@row < 1, Finish@row > TODAY(+5)), "Green", IF(AND([% Complete]@row < 1, Finish@row > TODAY()), "Yellow", IF(AND([% Complete]@row < 1, Finish@row <= TODAY()), "Red")))))

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.