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

Pamela Campo

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!

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