Check for blank cell in "IF(AND" statement isn't working

Bethany Garcia

I have a formula that was working until I added a statement to one of the IF(AND sections to check for whether a certain cell is blank. I've also tried using ISBLANK and that didn't work either. The condition I updated that is causing the formula to fail is IF(AND([Temp Move 2 Move In Date]@row < TODAY(), [Perm Unit Move In Date]@row = “”), “Occupied”, where I'm trying to check for whether one date field is less than today and another date field is blank to return that value.

=IF([Temp Move 2 Move In Date]@row = "", "Vacant", IF([Temp Move 2 Move In Date]@row = TODAY(), "Occupied", IF([Temp Move 2 Move In Date]@row > TODAY(), "Vacant", IF(AND([Temp Move 2 Move In Date]@row < TODAY(), [Perm Unit Move In Date]@row >= TODAY()), "Occupied", IF(AND([Temp Move 2 Move In Date]@row < TODAY(), [Perm Unit Move In Date]@row = “”), “Occupied”, IF(AND([Temp Move 2 Move In Date]@row < TODAY(), [Perm Unit Move In Date]@row < TODAY()), "Vacant", "Error")))))

John Jonassen

Hi @Bethany Garcia

Give this formula a shot.

=IF(OR([Temp Move 2 Move In Date]@row = "", TODAY() < [Temp Move 2 Move In Date]@row, AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() > [Perm Unit Move In Date]@row)), "Vacant", IF(OR([Temp Move 2 Move In Date]@row = TODAY(), AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() <= [Perm Unit Move In Date]@row), AND(TODAY() > [Temp Move 2 Move In Date]@row, [Perm Unit Move In Date]@row = "")), "Occupied", "Error"))

Mike Wilday

To explain the differences between both of these formulas, you should be aware that when you open an AND statement in an IF formula you want to close that AND statement when you're through otherwise it will look like your else statements in the IF formula are just part of the And statement.

example: IF(And(this, that, the other thing), "Then this", "Else this")

Bethany Garcia

https://community.smartsheet.com/discussion/comment/269447#Comment_269447

@John Jonassen The formula works great for every condition except "AND(TODAY() > [Temp Move 2 Move In Date]@row, [Perm Unit Move In Date]@row = "")", it's returning Vacant instead of Occupied when Perm Unit Move In Date is blank and Temp Move 2 Move In Date is less than today.

I removed each part of the Vacant logic to figure out which one is catching it, and it looks like it's treating a blank Perm Unit Move In Date the same as it being less than today. I tried to add an additional set of criteria to the statement "AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() > [Perm Unit Move In Date]@row)" to validate that Perm Unit Move In Date is not blank so it will stop returning Vacant in the situation of Perm Unit Move In Date being blank, but it's giving me an error. Any ideas on how to get that logic to not apply to a blank Perm Unit Move In Date?

Mike Wilday

Try using ISBLANK([Perm Unit Move in Date]@row)

=IF(OR([Temp Move 2 Move In Date]@row = "", TODAY() < [Temp Move 2 Move In Date]@row, AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() > [Perm Unit Move In Date]@row)), "Vacant", IF(OR([Temp Move 2 Move In Date]@row = TODAY(), AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() <= [Perm Unit Move In Date]@row), AND(TODAY() > [Temp Move 2 Move In Date]@row, ISBLANK([Perm Unit Move In Date]@row))), "Occupied", "Error"))

Did that work? If not, we'll need to deconstruct that.

Bethany Garcia

@Mike Wilday It worked for all test criteria except that same one I'm running into, where it is treating a blank Perm Unit Move In Date as if it were less than today, so it's giving Vacant instead of Occupied for this criteria: AND(TODAY() > [Temp Move 2 Move In Date]@row, ISBLANK([Perm Unit Move In Date]@row))), "Occupied".

I had the idea of using a helper column if the issue is that it won't accept the ISBLANK or "", so I set up a column to just return a value of Blank or Not Blank for Perm Unit Move In Date, and then updated the formula to look for the value of that cell to be Blank, but that isn't working either, I'm still getting the Syntax error message. Here's my updated formula with that attempt:

=IF(OR([Temp Move 2 Move In Date]@row = "", TODAY() < [Temp Move 2 Move In Date]@row, AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() > [Perm Unit Move In Date]@row)), "Vacant", IF(OR([Temp Move 2 Move In Date]@row = TODAY(), AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() <= [Perm Unit Move In Date]@row), AND(TODAY() > [Temp Move 2 Move In Date]@row, [Perm Unit Move In Date Blank or Not]@row = “Blank”)), "Occupied", "Error"))

Mike Wilday

Sometimes when a certain criteria is firing before another, then switching the statements around might help. In this version I moved the Occupied statement before the vacant one. (I used my last post to you.) Does this work any differently?

=IF(OR([Temp Move 2 Move In Date]@row = TODAY(), AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() <= [Perm Unit Move In Date]@row), AND(TODAY() > [Temp Move 2 Move In Date]@row, ISBLANK([Perm Unit Move In Date]@row))), "Occupied", IF(OR([Temp Move 2 Move In Date]@row = "", TODAY() < [Temp Move 2 Move In Date]@row, AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() > [Perm Unit Move In Date]@row)), "Vacant", "Error"))

Bethany Garcia

@Mike Wilday The formula you sent didn't work - well, it did work for the one line I was having problems with but it just caused the same problem for another row of logic. I broke it out line by line, tested each piece, reordered them and put it back together and that seems to have done the trick, this formula works:

=IF([Temp Move 2 Move In Date]@row = "", "Vacant", IF(AND(TODAY() > [Temp Move 2 Move In Date]@row, ISBLANK([Perm Unit Move In Date]@row)), "Occupied", IF([Temp Move 2 Move In Date]@row = TODAY(), "Occupied", IF([Temp Move 2 Move In Date]@row > TODAY(), "Vacant", IF(AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() <= [Perm Unit Move In Date]@row), "Occupied", IF(AND(TODAY() > [Temp Move 2 Move In Date]@row, TODAY() > [Perm Unit Move In Date]@row), "Vacant", "Error"))))))

Mike Wilday

Awesome. Yeah, IF Formulas are tricky as the first TRUE option will fire so the set up matters a lot. You have to check each one and consider if each one part will be true even if the other part isn't? It goes in order through the statements. A common misconception with IF statements is that it looks at the entire statement and chooses the one that best fits the condition. This isn't true. IF statements look consecutively at each statement and once it finds a first true statement, then it fires that answer and stops. I'll try to break it down below.

IF statement 1 (is this true) YES, then post that response (If not, look at IF statement 2).

IF statement 2 (Is this true) YES, Then post that response (if not look at IF statement 3).

IF statement 3 (Is this true) YES, Then post that response (if not, look at IF statement 4 or the else statement)

In your case, you must have several cases that might be considered TRUE and so it fires the first one it finds. 😁 I hope that helps!