Skip to content
  • Try Smartsheet

    • 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.

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

    ✭✭
    in Formulas and Functions

    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) 

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • ✭✭✭✭✭
      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:

      image.png

      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!

      · Share on FacebookShare on Twitter
    • ✭✭
      Answer ✓

      Hi Brett,

      It worked like magic!!

      Many thanks!

      Abiola

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭
      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:

      image.png

      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!

      · Share on FacebookShare on Twitter
    • ✭✭
      Answer ✓

      Hi Brett,

      It worked like magic!!

      Many thanks!

      Abiola

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @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!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions