Hi, can anyone help me to get my Green/Yellow/Red/Blue Status to work correctly?

LearningUser

I have to write a green/yellow/red/blue status indicator with a risk override field as well. The concept is that if the Risk Override field is populated then that should be the status. The risk override field will be a drop down between Red and Yellow. If that is not populated, then it should be

Blue if % complete is 1 (100%),

Red if Today is after the Baseline End Date and the task is not complete

Yellow if it has not exceeded the Baseline End Date but, it started late or should have started, but has not started yet.

Green if it is not started yet and Today isn't the Baseline Start Date or if it starts on time and has not went past the Baseline End Date but and is not complete yet.

Fields I am working with: Status, Risk Override, Actual Start Date, Actual End Date, Baseline Start Date, Baseline End Date, % Complete

So far, I have written the statement below. It doesn't work correctly for all the scenerio's like yellow:

=IF([% Complete]@row = "1", "Blue", IF(AND(OR(ISBLANK([Actual Start Date]@row), [Actual Start Date]@row > [Baseline Start Date]@row), AND([% Complete]@row < "1")), "Yellow", IF(AND([Actual Start Date]@row <= [Baseline Start Date]@row, [Baseline End Date]@row > TODAY()), "Green", IF(AND([% Complete]@row < "1", [Baseline End Date]@row < TODAY())), "Red")))

Thank you for any assistance you can give me.

Kelly Moore

Hey @LearningUser

I moved a few parentheses around and switched the order of the Red-IF in the sequence.

Try this

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < 1, [Baseline End Date]@row < TODAY()), "Red", IF(AND(OR(ISBLANK([Actual Start Date]@row), [Actual Start Date]@row > [Baseline Start Date]@row), [% Complete]@row < 1), "Yellow", IF(AND([Actual Start Date]@row <= [Baseline Start Date]@row, [Baseline End Date]@row > TODAY()), "Green"))))

cheers,

Kelly

LearningUser

Hi, when I entered the formula above, it still have red and yellow calculating wrong. It has future start dates as yellow and todays end date was red.

Kelly Moore

@LearningUser

I tweaked it. See if this works for you

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < "1", [Baseline End Date]@row <= TODAY()), "Red", IF(OR(AND(TODAY() < [Baseline End Date]@row, TODAY() >= [Baseline Start Date]@row, ISBLANK([Actual Start Date]@row), [% Complete]@row < 1), AND(TODAY() < [Baseline End Date]@row, TODAY() >= [Baseline Start Date]@row, [Actual Start Date]@row >= [Baseline Start Date]@row, [% Complete]@row < 1)), "Yellow", IF(AND([Actual Start Date]@row < [Baseline Start Date]@row, [Baseline End Date]@row > TODAY()), "Green"))))

If it isn't quite right, check if an equal sign needs to be added or removed to the less than or greater than (the equal sign will always follow the less than/greater than sign).

Let me know

Kelly

LearningUser

Hi Kelly. We are very close. Everything works correctly except when the task starts on the exact baseline date it is supposed to start on. Then it makes it yellow. It is green if it starts before. It goes red when past the due date. It is blue when 100% complete. But the yellow picks up starting exactly on time as yellow.

Kelly Moore

Hey @LearningUser

Yes, that was the 'or equal to' I wasn't certain how you wanted handled.

=IF([% Complete]@row = 1, "Blue", IF(AND([% Complete]@row < "1", [Baseline End Date]@row <= TODAY()), "Red", IF(OR(AND(TODAY() < [Baseline End Date]@row, TODAY() >= [Baseline Start Date]@row, ISBLANK([Actual Start Date]@row), [% Complete]@row < 1), AND(TODAY() < [Baseline End Date]@row, TODAY() > [Baseline Start Date]@row, [Actual Start Date]@row > [Baseline Start Date]@row, [% Complete]@row < 1)), "Yellow", IF(AND([Actual Start Date]@row < [Baseline Start Date]@row, [Baseline End Date]@row > TODAY()), "Green"))))

Try this one. I eliminated both of the 'equals' in the Yellow-IF.