Looking to have a RAG status based on Status and Date

Brian S Lee

Hi all- Looking for some help. Trying to create a RAG status (red, yellow, green, blue) that takes into account an expected completion date as well as a status (Not Started, In Progress, Pending, and Complete)

Rules:

Task in Complete state should be Blue

Task that is in Progress or Pending before the Expected completion date is Green

Task that is in Progress or Pending up to 2 days after the Expected completion date is Yellow

Task that is Not Started and Today = Expected Completion date is Red

Task that is not in Complete State and 3 days past Expected Completion date is Red.

Thank you.

Paul Newcome

Try this:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", IF([Expected Completion Date]@row<= TODAY(), "Red", "Green"), IF([Expected Completion Date]@row>= TODAY(), "Green", IF([Expected Completion Date]@row>= TODAY(-2), "Yellow", "Red"))))

Brian S Lee

Thank you so much!

Paul Newcome

Happy to help. 👍️