Flag should turn red whenever over due day is over 30 days and status is not completed

Hi everyone,

I have a 'risk' column with flag, it is supposed to turn red whenever status does not indicate 'completed' and 'due day' is already beyond 30days, except if the status was cancelled.

I used the formula below but all the flags turned red automatically, I do not know what I am doing wrong, please help:

What is the best way to use current user guide that everyone responsible for task will only see their task and not concern with others?

=IF(AND([Days to due Date]@row < -30, Status@row <> "Completed"), 1, 0) 

Best Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    Answer ✓

    Hey Abiola,

    You're so close! You don't need a "-30", you need a positive 30.

    Also, if you're wanting to add "Cancelled" in there as a status, here's the formula:

    =IF(Status@row = "Cancelled", 0, IF(AND([Days to due Date]@row > 30, Status@row <> "Completed"), 1, 0))

    You had stated "Except if the status is cancelled" in bold, so I figured that was important.

    Here's what it should look like:

    Please let me know if this formula 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!

  • Abiola
    Abiola ✭✭
    Answer ✓

    Hi Brett,

    It worked like magic!!

    Many thanks!

    Abiola

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭
    Answer ✓

    Hey Abiola,

    You're so close! You don't need a "-30", you need a positive 30.

    Also, if you're wanting to add "Cancelled" in there as a status, here's the formula:

    =IF(Status@row = "Cancelled", 0, IF(AND([Days to due Date]@row > 30, Status@row <> "Completed"), 1, 0))

    You had stated "Except if the status is cancelled" in bold, so I figured that was important.

    Here's what it should look like:

    Please let me know if this formula 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!

  • Abiola
    Abiola ✭✭
    Answer ✓

    Hi Brett,

    It worked like magic!!

    Many thanks!

    Abiola

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭

    @Abiola 👍👍 Glad to help!

    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!