Getting Message Column Formula Syntax Isn't Right

Bethany Garcia

I'm trying to put the below formula into a column formula and continue to get this error: "The column formula syntax isn’t quite right, see our help article https://help.smartsheet.com/articles/2481944 for more information.". I've tried everything I can think of and it just isn't working. I've tried breaking it into pieces to find the part that isn't working, the first IF statement is successful but all of the following are giving the error, making me wonder if it has to do with my use of TODAY().

=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 <TODAY()), "Vacant", "Error")))))

Mark Cronk

Hi Bethany,

Try starting the formula with IFERROR( and an error response at the end.

Mark

Andrée Starå

Hi @Bethany Garcia

There were multiple errors in your formula. One thing was that the wrong characters were surrounding the second IF part and Occupied.

Try something like this.

=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 < TODAY()), "Vacant", "Error")))))

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Bethany Garcia

@Andrée Starå your formula worked except that it is returning a value that I don't expect it to and I think I need to add a new line of If/And statements. I tried adding it and copying it from what you sent but I'm still getting an error and cannot figure out what is causing it. Here is my updated formula, the added portion is "IF(AND([Temp Move 2 Move In Date]@row < TODAY(), [Perm Unit Move In Date]@row = “”), “Occupied”". What was happening is that if I had the Temp Move 2 Move In Date populated with a date less than today and Perm Unit Move In Date was blank it was returning Vacant, when it should return Occupied, so I'm adding a line to account for that situation of the Perm Unit Move In Date being blank.

=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")))))

Andrée Starå

@Bethany Garcia

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Bethany Garcia

@Andrée Starå do you have any insight into why my formula I posted on 11/16 wouldn't work? I've been trying it with ISBLANK as well but it doesn't seem to work. This is the primary line I'm trying to figure out is IF(AND([Temp Move 2 Move In Date]@row < TODAY(), [Perm Unit Move In Date]@row = “”), “Occupied”, but it doesn't seem to want to work to include a condition for a cell being blank with the AND statement.

Andrée Starå

@Bethany Garcia

Did you get it working?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Bethany Garcia

I did, thank you!

Andrée Starå

@Bethany Garcia

Excellent!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!