Need help with IF AND function

Megan Hochstrasser

Here's the logic I'm going for—if a request is over a week old AND the "Request complete" checkbox isn't checked, then the row should be marked as overdue.

The following formula for the "over a week old" part works:

=IF((TODAY() - [Request Date]1) - 7 > 0, "overdue", "")

This single formula for "Request complete" not being checked works:

=IF([Request Complete]1 = 0, "overdue", "")

But I can't seem to combine them. This is what I'm trying:

=IF(AND((TODAY() - [Request Date]1) - 7 > 0, [Request Complete]1 = 0), “overdue”, “”)

This is #UNPARSEABLE. What am I doing wrong?

Mark Cronk

We will win.

The error is caused by things like a misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Delete the formula. Save your sheet. Enter the formula again.

Go through the formula and confirm the column names are correct. When your cursor is in the formula the row names and the referenced cella should light up. If they don't there's a typo.

Good luck.

Mark

Mark Cronk

Try:

=IF(AND(TODAY(-7) > [Request Date]@row , [Request Complete]@row = 0), “overdue”, “”)

Work?

Mark

Megan Hochstrasser

Still unparseable :(

Mark Cronk

Hi Megan,

Hmmm. Try:

=IF(ISDATE([request date]@row), IF(AND(TODAY(-7) > [Request Date]@row , [Request Complete]@row = 0), “overdue”, “ "))

Mark

Megan Hochstrasser

Unparseable! It seems so simple, not sure what's going wrong

Mark Cronk

We will win.

The error is caused by things like a misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Delete the formula. Save your sheet. Enter the formula again.

Go through the formula and confirm the column names are correct. When your cursor is in the formula the row names and the referenced cella should light up. If they don't there's a typo.

Good luck.

Mark

Megan Hochstrasser

OMG! My original formula actually works now. I refreshed a bunch of times and typed it out manually, and one time it just decided to work. So bizarre.

Thank you for the tips!

On a related note, I'd been wondering about the coloring/lighting up function, actually. It seems to work sometimes but not always, and I'm 100% typing things correctly. Sometimes if I paste a formula, SmartSheet doesn't recognize column/cell names, but if I retype them, sometimes it does, sometimes it doesn't. If the lighting up doesn't happen, does that necessarily mean the formula won't work? I assumed that this SmartSheet feature was just being janky and not displaying, but that it would still understand the formula. After this particular experience, it seems that if this highlighting doesn't happen, something may be wrong with SmartSheet, so it's a good indicator that the formula will fail.

Mark Cronk

Glad you found a solution and your formula cooperated. If your formula doesn't light up there is probably an issue. Cross sheet references don't light up. Formulas using cells or rows on the same sheet should.

Good luck!

Mark