If formula with nested AND, OR, NOT ISBLANK within

Options

Not sure what I did wrong as when I'm reading through the formulat i wrote it seems correct, but I'm not getting the correct color value.

Essentially I need: if the status is "Not Started" OR "In Process" OR blank AND the "End Date" is not blank OR is less than today's date, turn red. If the "End Date" is blank=Gray", otherwise Green.

It does exactly what I need except when both "status" & "end date" are blank, I'm getting red as the output and I want gray. What am I missing?


=IF(AND(OR(Status@row = "Not Started", Status@row = "In Process", Status@row = ""), OR([End Date]@row < TODAY(), NOT(ISBLANK([End Date]@row)))), "Red", IF([End Date]@row = "", "Gray", "Green"))

Tags:

Best Answer

  • Jessica Abbey
    Jessica Abbey ✭✭✭
    Answer ✓
    Options

    Is end date cell was populated but the status was not, I am getting incorrect argument. But seeing your formula helped me figure out what to do! Thanks!

    Final Formula:

    =IF(ISBLANK([End Date]@row), "Gray", IF(OR(AND(OR([End Date]@row < TODAY(), [End Date]@row = TODAY()), Status@row = "Complete"), [End Date]@row > TODAY()), "Green", IF([End Date]@row = TODAY(), "Red")))

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi,

    Try this:

    =IF(ISBLANK([end date]@row), "Gray", IF(AND(OR(Status@row = "Not Started", Status@row = "In Process", ISBLANK(Status@row)), OR([End Date]@row < TODAY(), NOT(ISBLANK([End Date]@row))), "Red", "Green"))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Jessica Abbey
    Jessica Abbey ✭✭✭
    Answer ✓
    Options

    Is end date cell was populated but the status was not, I am getting incorrect argument. But seeing your formula helped me figure out what to do! Thanks!

    Final Formula:

    =IF(ISBLANK([End Date]@row), "Gray", IF(OR(AND(OR([End Date]@row < TODAY(), [End Date]@row = TODAY()), Status@row = "Complete"), [End Date]@row > TODAY()), "Green", IF([End Date]@row = TODAY(), "Red")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!