RAG Status

pjb

Hello! Newbie here :)

I was wondering if someone could help with a formula I'm attempting to put together to calculate a RAG symbol status for individual tasks as well as the overall project plan.

Individual Tasks:
If status is complete = blue
If status is in progress OR not started AND end date is today = yellow
If status is in progress OR not started AND end date is in the past = red
If status is not started AND start date is in the past = red
If status is in progress AND end date is in the future = green
=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "In Progress", [End Date]@row <= TODAY()), "Yellow", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row >= TODAY()), "Green", ""))))

Overall Project: calculates the overall status of the project based on the above.

Thanks in advance!

Paul Newcome

Try this:

=IF(Status@row = "Complete", "Blue", IF(OR([End Date]@row < TODAY(), AND(Status@row = "Not Started", [Start Date]@row < TODAY())), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green")))

Paul Newcome

Try this:

=IF(Status@row = "Complete", "Blue", IF(OR([End Date]@row < TODAY(), AND(Status@row = "Not Started", [Start Date]@row < TODAY())), "Red", IF([End Date]@row = TODAY(), "Yellow", "Green")))

pjb

Hi @Paul Newcome , thank you for responding! Figured it out in the end, with a few amendments:

=IF(Status@row = "Complete", "Blue", IF(AND(Status@row = "In Progress", [End Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "In Progress", [End Date]@row < TODAY()), "Red", IF(AND(Status@row = "In Progress", [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row = TODAY()), "Yellow", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY()), "Green", ""))))))

Have a great weekend!