ISBLANK combined with AND statements

Options
Blue Pencil
Blue Pencil ✭✭
edited 06/08/22 in Formulas and Functions

Hi, I'm having trouble nesting AND & OR statements with the ISBLANK condition in my IF statement.

I have two columns: [Agreed End Date] and [Actual End Date]

=IF(AND(NOT(ISBLANK([Actual End Date])), (OR(TODAY() - [Agreed End Date]@row < 1, "On Track", IF(TODAY() - [Agreed End Date]@row < 7, "Delay", IF(TODAY() - [Agreed End Date]@row > 7, "Overdue", "Error")))

I tried to split it into multiple IF statements rather than having the OR in place but it did not work neither. For example:

=IF(AND(TODAY() - [Agreed End Date]@row > 7, ISBLANK([Actual End Date]@row), "overdue", ""))

The above returns an "invalid argument set"

Answers

  • Genevieve P.
    Options

    Hi @Blue Pencil

    When nesting IF statements, I find it a little easier to look for Blanks or Not Blank by using = "" for equals blank, or <> "" for not blank. This eliminates the need for parentheses which can sometimes cause an error if they're in the wrong place.

    For example:

    =IF(OR(TODAY() - [Agreed End Date]@row > 7, [Actual End Date]@row = ""), "Overdue", "")


    You'll notice I closed off the OR statement before telling it what to do:

    =IF(OR(statement ), "Overdue", "")


    Then for your other formula, I'm not quite sure what you want it to do. Could you write out what you want it to do using words?

    Let me know if this is close:

    =IF(OR(TODAY() - [Agreed End Date]@row > 7, [Actual End Date]@row = ""), "Overdue", IF(TODAY() - [Agreed End Date]@row < 7, "Delay", IF(TODAY() - [Agreed End Date]@row < 1, "On Track", "Error")))


    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!