IF AND statement with TODAY function
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!
Answers
-
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
-
Thank you Heather! Yes, the Date column does have date column properties. I deleted and tried again and still says #invalid operation.
-
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!
-
Thank you for all your help! I will try it again!
-
Do you know if it matter is "Restrict to dates only" is checked or not under columns property?
Thanks!
-
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.
-
Ok thanks Heather!
-
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!
-
Actually you are right it should be when the date is in the past, so I changed it to < and it still didnt work.
-
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?
-
Thank you!
-
How is the date column populated?
-
There is a formula in the date column that pulls in the dates like this: 8/10/21
-
I think I will just do the date condition within the notification itself.
Thank you all for your help!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!