Hi, can anyone help me to get my Green/Yellow/Red/Blue Status to work correctly?
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.
Help Article Resources
Check out the Formula Handbook template!