# Set RAG based on % Complete & Deadline Date Proximity

✭✭✭✭✭✭

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

Tags:

## Best Answer

• ✭✭✭✭✭
Answer ✓

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!

If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

Love,

Brett Wyrick | Connect with me on LinkedIn.

------------------------------------------------------------------------------

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

## Answers

• ✭✭✭✭✭
Answer ✓

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!

If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

Love,

Brett Wyrick | Connect with me on LinkedIn.

------------------------------------------------------------------------------

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!