3 Conditions Formula

I want to have a flag turn red if 1. A task is considered a milestone, 2. The Planned End Date is today or before, and 3. The task is not done.


The below formula is not working:


=-IF(AND([Is Milestone]@row, 1, Done@row, 0, [Planned End Date]@row < TODAY()), 1, 0)

Best Answers

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Jennifer Fitzgerald

    It looks like this should work, except there's a small "-" between the = sign and the IF.

    Try this:

    =IF(AND([Is Milestone]@row, 1, Done@row, 0, [Planned End Date]@row <= TODAY()), 1, 0)


    (Note: I've also added an = sign before the TODAY function to say if it is today as well).

    Let me know if this works for you! If not, it would be helpful to see a screen capture of what it looks like in your sheet with the column names, etc. (but please block out any sensitive data).

    Cheers!

    Genevieve

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @Jennifer Flanagan ,

    to add to Genevieves advice regarding the minus and the =, you also have to remove the commas for the logical statements like you did with the date part:

    =IF(AND([Is Milestone]@row 1, Done@row 0, [Planned End Date]@row <= TODAY()), 1, 0)

    Hope this helps too

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Genevieve P.
    Genevieve P. Employee Admin

    Haha, me too, with the copy/paste error. Thanks for the save!!

  • Thanks so much! That works