Change Status and Health when % Complete is updated

kjex1970

Hi-

I'm struggling with trying to automatically change my status and health columns depending on date changes. Is the following do-able?

If % complete is = to 100% then status turns to "Complete" and my health symbol turns blue

If % complete is < 100% and my Finish date is greater than 7 days then status turns to "In Progress" and my health column symbol turns green

If % complete is < 50% and my Finish date is within 5 days then status turns to "At Risk" and my health column symbol turns yellow

If % complete is < 100% and my finish date is in the past then status turns to "Delayed" and my health column symbol turns red

I'm open to any advice you have!!

Thank you so much in advance!

kjex1970

Also, how do I add in "Not Started"? I've tried:

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", IF(AND([Percent Complete]@row > 0.5, [Finish Date]@row < TODAY(7)), "In Progress", “""))))))

So with the above, and with assuming I cannot have 5 colors, how do I account for "Not Started"? I'm using your formula:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "At Risk", "Yellow", IF(Status@row = "Delayed", "Red", IF(Status@row = "In Progress", "Green", ""))))

Whenever I try to add in a condition, I get Unparseable and I don't know what I"m doing wrong?

Thanks for any help you can give!!

Mike TV

Almost there. You left out one variable for over 50% complete and less than less than 7 days to Finish Date. However, for what you provided here's your answer:

Status column formula:

=IF([Percent Complete]@row = 1, "Complete", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", ""))))

Health column formula:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "At Risk", "Yellow", IF(Status@row = "Delayed", "Red", IF(Status@row = "In Progress", "Green", ""))))

kjex1970

Hi, the first one works somewhat but it's putting things in progress that should be not started or at risk and I just cannot figure out how to add those, plus the one you suggested in. I think if I can get the first one right, the second one will be fine. Also, can I have Red, Blue, Green, Yellow AND Gray? If not, how do I change the formula to use the five green, yellow, yellow, yellow, red arrows?

kjex1970

Also, how do I add in "Not Started"? I've tried:

=IF([Percent Complete]@row = 1, "Complete", IF([Percent Complete]@row = 0, "Not Started", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", IF(AND([Percent Complete]@row > 0.5, [Finish Date]@row < TODAY(7)), "In Progress", “""))))))

So with the above, and with assuming I cannot have 5 colors, how do I account for "Not Started"? I'm using your formula:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "At Risk", "Yellow", IF(Status@row = "Delayed", "Red", IF(Status@row = "In Progress", "Green", ""))))

Whenever I try to add in a condition, I get Unparseable and I don't know what I"m doing wrong?

Thanks for any help you can give!!

Mike TV

You need to set parameters for Not Started and further define In Progress. For "Not Started" would that be progress of 0%? For the ones being marked as "At Risk" erroneously, what is their % completed, their Finish Dates, and what status should they be marked as? For my example above that had 90% completion and a Finish Date of 05/05/2022 that has no Status, what Status should that full under?

Lastly, for the status colors on your sheet right-click your Status column and go to Edit Column Properties and make sure it's set to Symbols type. Scroll down to the Status section and look at the colored circle options. Those are your choices. The one with Blue doesn't have a Grey and the one with Grey doesn't have a Blue. You'll need to choose which one you want to work with. It can't be both.

Mike TV

I've taken a guess that you'd want Complete % greater than 50% and within the next 7 days with a Status of "In Progress". With that in mind it gives me this:

Status formula:

=IF([Percent Complete]@row = 0, "Not Started", IF([Percent Complete]@row = 1, "Complete", IF(AND([Percent Complete]@row < 1, [Finish Date]@row < TODAY()), "Delayed", IF(AND([Percent Complete]@row < 0.5, [Finish Date]@row <= TODAY(5)), "At Risk", IF(AND([Percent Complete]@row < 1, [Finish Date]@row > TODAY(7)), "In Progress", IF(AND([Percent Complete]@row > 0.5, [Finish Date]@row <= TODAY(7)), "In Progress", ""))))))

For "Not Started" Health symbol, there are only 4 colored symbols to play with. Since you've already assigned symbols to all four, the existing formula I used just leaves "Not Started" as a blank cell for it's Health. That should work shouldn't it?

Health formula (same as before):

=IF(Status@row = "Complete", "Blue", IF(Status@row = "At Risk", "Yellow", IF(Status@row = "Delayed", "Red", IF(Status@row = "In Progress", "Green", ""))))