Traffic light formula based on date and % complete

EricaUni

Hi, I'm wondering if someone is able to help adjust this formula slightly to include the following information, I just can't seem to get it right:

Current formula:

=IF([% Complete]@row = 1, "Gray", IF([Start Date]@row > TODAY(), "Green", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY(+2)), "Red", IF(AND([% Complete]@row < 1, [End Date]@row >= TODAY(-2)), "Yellow"))))

Updating to the following:

Green: Status is at Not started and % complete is at 0%

Red: Status is at Not Started or In Progress, passed its due date and % complete is less than 100% or In Progress and less than 80% 2 days before due date.

Yellow: Status is In Progress and less than 100% either before or after the start date

Grey: Status is Complete and % complete is at 100%

Thank you!

Paul McGuinness

HI @EricaUni

This formula should do the trick for you

=IF(AND(Status@row = "Complete", [% Complete]@row = 1), "Gray", IF(AND(Status@row = "Not Started", [% Complete]@row = 0), "Green", IF(OR(AND(Status@row = "In Progress", [% Complete]@row < 0.81, TODAY() - [End date]@row < 3), AND(OR(Status@row = "In Progress", Status@row = "Not Started"), [% Complete]@row < 1, [End date]@row < TODAY())), "Red", IF(AND(Status@row = "In Progress", [% Complete]@row < 1), "Yellow", "Error"))))

Tested and working based on your requirements

Hope that helps

Thanks

Paul

Paul McGuinness

HI @EricaUni

This formula should do the trick for you

=IF(AND(Status@row = "Complete", [% Complete]@row = 1), "Gray", IF(AND(Status@row = "Not Started", [% Complete]@row = 0), "Green", IF(OR(AND(Status@row = "In Progress", [% Complete]@row < 0.81, TODAY() - [End date]@row < 3), AND(OR(Status@row = "In Progress", Status@row = "Not Started"), [% Complete]@row < 1, [End date]@row < TODAY())), "Red", IF(AND(Status@row = "In Progress", [% Complete]@row < 1), "Yellow", "Error"))))

Tested and working based on your requirements

Hope that helps

Thanks

Paul