At Risk Flag

Hi there! I am trying to have it so that the flag shows if the End Date is 3 days away AND the Status is "Not Started" (ideally, I would like it to show if the status is anything but Completed, but that may be too difficult). I know to use a nested IF/AND function, but I keep getting errors when I write it out. I started with: =IF(AND([End Date]@row < TODAY() - 3, [email protected] = "Not Started", 1, 0)) and have been messing with that a little and still no luck. This is only a sample sheet I am presenting on Monday, that's why there aren't many dates in there. Thanks in advance!

Screen Shot 2019-11-08 at 3.40.08 PM.png

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Very close! You can use <> to mean does not equal, and you have to close the AND statement after your last criterion. Try this one:

    =IF(AND([End Date]@row < TODAY() - 3, [email protected] <> "Completed"), 1, 0)

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Actually, I would also revise your today statement. You're today statement says less than today - 3 days ago... not three days into the future.... this one should meet that requirement too. 

    =IF(AND([End Date]@row < TODAY(3), [email protected] <> "Completed"), 1, 0)

    It's looking at if your end date is less than 3 days from today. 

  • So close, yet so wrong haha. Thank you so much!

  • Is there a way to have it so if there is no date, no flag will show? That's what I thought the AND function was for. Thanks!

    Annotation 2019-11-11 082906.jpg

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

    Hi,

    Try something like this in the beginning.

    =IF(NOT(ISDATE([End Date]@row)); 0; YOUR OTHER FORMULA

    The same version but with the below changes for your and others convenience.

    =IF(NOT(ISDATE([End Date]@row)), 0, YOUR OTHER FORMULA

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • That did the trick!! Awesome, thanks for your help. 

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

    Excellent!

    Happy to help!

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You're welcome. It looks like Andre answered your second question. Let us know if you need additional help!