Smartsheet at- risk formula

06/22/20
Accepted

Hi all, I'm having a little trouble with creating the right formula for my "at risk" / "attn" column.

I only want the column to flag is the status is not complete and the target end date is past due. My current formula is =IF(AND(Status301 <> "Complete", [Target End Date]301 <= TODAY()), 1, 0)

Currently, the column flags even when the target end date is blank. I only want the flag when the status is not complete, and the target end date is past due, AND the target end date is not blank.

Please help!


Thanks

Best Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Try somethingn like this...

    =IF([Target End Date]301 <> "", IF(AND(Status301 <> "Complete", [Target End Date]301 <= TODAY()), 1, 0))


    This will take your original formula and run it only if the [Target End Date] is not blank.

    thinkspi.com

  • Titilope AkinloseTitilope Akinlose ✭✭✭✭✭
    Accepted Answer

    Hi Paul,


    I have an updated formula I would like to use, and I ma having trouble and not sure whether to use the AND/OR feature.  I only want the flag when the:

    1. End date is not blank
    2. End date is in the past
    3. And status is not complete OR received.

    Current formula is you helped me with is

    =IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", [End Date]1 <= TODAY()), 1, 0))

    Not sure how to incorporate the status is not complete OR received component.

    Any help will be appreciated.

    Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Try this...

    =IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", Status1 <> "Received", [End Date]1 <= TODAY()), 1, 0))

    thinkspi.com

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Try somethingn like this...

    =IF([Target End Date]301 <> "", IF(AND(Status301 <> "Complete", [Target End Date]301 <= TODAY()), 1, 0))


    This will take your original formula and run it only if the [Target End Date] is not blank.

    thinkspi.com

  • Titilope AkinloseTitilope Akinlose ✭✭✭✭✭

    Thank you so much, it worked perfectly!!!!!!!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

    thinkspi.com

  • Titilope AkinloseTitilope Akinlose ✭✭✭✭✭
    Accepted Answer

    Hi Paul,


    I have an updated formula I would like to use, and I ma having trouble and not sure whether to use the AND/OR feature.  I only want the flag when the:

    1. End date is not blank
    2. End date is in the past
    3. And status is not complete OR received.

    Current formula is you helped me with is

    =IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", [End Date]1 <= TODAY()), 1, 0))

    Not sure how to incorporate the status is not complete OR received component.

    Any help will be appreciated.

    Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Try this...

    =IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", Status1 <> "Received", [End Date]1 <= TODAY()), 1, 0))

    thinkspi.com

  • Titilope AkinloseTitilope Akinlose ✭✭✭✭✭

    You are a master! Thank you!

Sign In or Register to comment.