Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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!

Trending in Formulas and Functions