Set RAG based on % Complete & Deadline Date Proximity

Cheryl Collins

Hi

I am trying to get my formula to set a RAG status based on the % complete and deadline date proximity. My formula is:

However, the last entry with a deadline date of 12/11 show be showing a "Yellow" RAG but, it still shows "Red". What have I missed?

TIA

Cheryl

Brett Wyrick

Hey Cheryl,

You can simplify this a bit:

=IF([Deadline Date]@row > (TODAY() + 21), "Green", IF(AND([% Complete]@row <>"Full", [Deadline Date]@row > (TODAY() + 10)), "Yellow", "Red"))

(next time, please copy/paste your formula 😊)

You can utilize "<>" which means "is not equal to", so for this formula, you can say "The percent complete column is not equal to 'Full'".

---

However, your 12/11/21 date is 43 days from today (10/29/21), so it technically should be 🟢Green, not 🟡Yellow - right? Since your initial IF statement says that cells with a value higher than Today's date + 21 days should be Green, that one should be 🟢Green, right?

A date like 11/11/21 would be 🟡Yellow since it'd be within the 21 days (13 days from today's date).

---

Let me know if this works for you!

‎

Brett Wyrick

Hey Cheryl,

You can simplify this a bit:

=IF([Deadline Date]@row > (TODAY() + 21), "Green", IF(AND([% Complete]@row <>"Full", [Deadline Date]@row > (TODAY() + 10)), "Yellow", "Red"))

(next time, please copy/paste your formula 😊)

You can utilize "<>" which means "is not equal to", so for this formula, you can say "The percent complete column is not equal to 'Full'".

---

However, your 12/11/21 date is 43 days from today (10/29/21), so it technically should be 🟢Green, not 🟡Yellow - right? Since your initial IF statement says that cells with a value higher than Today's date + 21 days should be Green, that one should be 🟢Green, right?

A date like 11/11/21 would be 🟡Yellow since it'd be within the 21 days (13 days from today's date).

---

Let me know if this works for you!

‎