RAG Rating Formulas

handmadetsunami

Hi,

I am struggling with RAG rating formulas (and its something that I have done before but I can't get my head around it at the moment!)

I am trying to do the following tasks:

Regardless of the end date, if % complete is 100%, then show a green rag rating

If the finish date is within 30 days and the % complete isn't 100%, then show a yellow rag rating

If the finish date is within 15 days or in the past and the % complete isn't 100%, then show a red rag rating

I have attached a screenshot to show how the sheet is laid out

 

Mike Wilday

Try this: 

=IF([% Complete]@row=1, "Green", IF(AND(Finish@row < Today(30), [% Complete]@row < 1), "Red", IF(AND(Finish@row < TODAY(30), [% Complete]@row < 1), "Yellow","")