Set RAG based on % Complete & Deadline Date Proximity

10/29/21
Accepted

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

  • Brett WyrickBrett Wyrick ✭✭✭
    Accepted 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.

    🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

    ❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

Answers

  • Brett WyrickBrett Wyrick ✭✭✭
    Accepted 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.

    🧔 Brett Wyrick; your friendly neighborhood freelance consultant & Smartsheet helper.

    ❓ Need some more help? Want to automate systems outside of Smartsheet to connect to your Smartsheet? Want to automate your sheets to talk to one another easier? Other questions? Send me an email or connect with me on LinkedIn.‎

Sign In or Register to comment.