IF AND statement with TODAY function

Kendra Hillmer

I am trying to write a formula where if the date in the "Date" column is greater than or equal to today AND the "Attended" column checkbox is not checked, then check the box in the "Missed" column box so that a notification will go out to all of those who missed the training.

This is what I have that is giving me an #INVALID

=IF(AND(Date@row >= TODAY(), Attended@row = 0), 1, 0)

Thank you for your help!

Heather Duff

Hi @Kendra Hillmer

That formula looks like it should work. Is your Date column set as a date column within the column properties? If not, the formula won't be able to compare it with the TODAY() function.

Let me know!

Best,

Heather

Kendra Hillmer

Thank you Heather! Yes, the Date column does have date column properties. I deleted and tried again and still says #invalid operation.

Heather Duff

@Kendra Hillmer

Hmm, I'm puzzled. I was able to recreate it, and it didn't give me an error. Here's what I used:

=IF(AND(Date@row >= TODAY(), Attended@row = 0), 1, 0)

Not sure where it's getting the error from. Maybe try typing the formula in again? Sometimes, for some strange reason, that irons out the wrinkles for me.

Also - the formula listed above looks for dates that are either equal to today or after today. If you're looking for dates that are in the past, you'll want to use < instead of >=.

Let me know if it still gives you trouble. I'm stumped!

Kendra Hillmer

Thank you for all your help! I will try it again!

Kendra Hillmer

Do you know if it matter is "Restrict to dates only" is checked or not under columns property?

Thanks!

Heather Duff

No, that shouldn't cause any problems. If there's something other than a date entered in the date column, it may throw an error in the checkbox column...but other than that it shouldn't be an issue.

Kendra Hillmer

Ok thanks Heather!

Kendra Hillmer

Sorry one more thing!

When I use this formula it works:

=IF(AND([Date]@row = TODAY(), [Attended]@row = 0), 1, 0)

When I use this one I get the #invalid operation:

=IF(AND([Date]@row > TODAY(), [Attended]@row = 0), 1, 0)

Any idea??

I decided to have the "Missed" box be checked when the date is greater than Today instead of greater than or equal to

Thanks!

Kendra Hillmer

Actually you are right it should be when the date is in the past, so I changed it to < and it still didnt work.

Heather Duff

Rats - that was going to be my next suggestion.

@Paul Newcome @Andrée Starå Calling in the pros on this. Any thoughts on why =TODAY() would work, but < or > TODAY() would throw an #INVALID OPERATION message?

Kendra Hillmer

Thank you!

Paul Newcome

How is the date column populated?

Kendra Hillmer

There is a formula in the date column that pulls in the dates like this: 8/10/21

Kendra Hillmer

I think I will just do the date condition within the notification itself.

Thank you all for your help!

Paul Newcome

OK. It almost sounds like the data isn't being stored as date values. If you change your mind and want to dig a little deeper into it, please feel free to let us know.