Using IF and ISBANK.. Please help!

Please help. I am currently using AT-RISK indicators for certain tasks that have not been closed.

The first formula works but does not turn off on the indicator once the task is closed.

=IF([Committed Closure Date]@row - TODAY() > 10, "Green", IF(AND([Committed Closure Date]@row - TODAY() >= 6, [Committed Closure Date]@row - TODAY() <= 10), "Yellow", IF([Committed Closure Date]@row - TODAY() <= 5, "Red")))


I am trying to create another formula that will not show the indicators once the Closure Date column contains a date and no longer blank. This is what I have so far which has not worked for me. Please help.

=IF(AND(ISBLANK([Closure Date]@row), [Committed Closure Date]@row - TODAY() > 10, "Green", IF(AND(AND(ISBLANK([Closure Date]@row), [Committed Closure Date]@row - TODAY() >= 6, [Committed Closure Date]@row - TODAY() <= 10), "Yellow", IF(AND(ISBLANK([Closure Date]@row), [Committed Closure Date]@row - TODAY() <= 5, "Red"))))))

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Dami

    I hope you're well and safe!

    Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Dami
    Dami ✭✭
    edited 04/26/23

    Hi Andree-

    Thank you so much. Please see screenshots below. As you will see, the indicators show even though the task is closed. The current formula works. But now I am trying to change it to one where the AT RISK column is blank once a closure date has been entered. I tried the below but it did not work:

    =IF(AND(ISBLANK([Closure Date]@row), [Committed Closure Date]@row - TODAY() > 10, "Green", IF(AND(AND(ISBLANK([Closure Date]@row), [Committed Closure Date]@row - TODAY() >= 6, [Committed Closure Date]@row - TODAY() <= 10), "Yellow", IF(AND(ISBLANK([Closure Date]@row), [Committed Closure Date]@row - TODAY() <= 5, "Red"))))))


  • Dami
    Dami ✭✭

    HI @Andrée Starå

    Are you able to help with the above? Thanks.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!