IF > TODAY formula not working

Dana Grummer

I've been trying for hours to get this formula right and I think I've tried every combination.

I have a date field. It can either

• Have a date
• Be Blank
• Have TBD
• Have N/A

If it has a date AND the date greater than today OR if blank OR if it's TBD = not check

If N/A OR date is passed OR it's blank = checked

This is what I have currently - and with the field blank it's checking the box. If I have N/A its giving me Invalid Operation

=IF(OR([Gate 3]@row = "N/A", [Gate 3]@row < TODAY()), 1, 0)

Paul Newcome

You have "if blank" in the requirements for both checked and unchecked. Which one should it be?

If it has a date AND the date greater than today OR if blank OR if it's TBD = not check

If N/A OR date is passed OR it's blank = checked

Dana Grummer

Sorry - I've been looking at the screen too long - if it's blank it should be unchecked.

Paul Newcome

Ok. Try this...

=IF(OR([Gate 3]@row >= TODAY(), [Gate 3]@row = "", [Gate 3]@row = "TBD"), 0, 1)

Dana Grummer

Thanks. It works until I have either TBD or N/A in the field. If it has TBD or N/A I'm getting Invalid Operation. Is it because the field is a date field and the data is a non-date?

Paul Newcome

It shouldn't be. Try removing the formula, logging out of Smartsheet, clearing the browser's cookies and cache, logging back in, then finally manually retyping the formula back in.

Dana Grummer

Did all that - actually had to reboot my pc b/c of other issues and I'm still getting Invalid

Am I missing something? Thanks for your help!

Paul Newcome

You shouldn't be getting that error.

Let's try breaking it down into multiple sections...

=IF(OR([Gate 3]@row >= TODAY(), [Gate 3]@row = ""), 0, IF([Gate 3]@row = "TBD", 0, 1)

Dana Grummer

That didn't work either - Got Invalid. I tried just the first part =IF(OR([Gate 3]@row >= TODAY(), [Gate 3]@row = ""), 0,1) and that worked. But when I add the ck for a string I get Invalid.

I also tried this - but it did NOT check the box when the date had passed. This is when I figured out I needed an OR statement.

=IF([Gate 3]@row = "TBD", 0, IF([Gate 3]@row = "N/A", 1, IF([Gate 3]@row >= TODAY(), 0)))

Paul Newcome

This is very odd. Hmm...

I am going to suggest reaching out to Support and providing them a link to this thread so they can see what troubleshooting steps we have already taken.

I'm sorry I can't be of more help, but at this point we are getting some very unexpected behavior.