Date validation formula referencing Sheet Summary fields
Hello,
I'm trying to update a date validation formula to include a reference to two Sheet Summary fields. The formula seems to be working -- except for the piece that references these Sheet Summary fields (in bold):
=IF(OR([Preferred Date*]@row = "09/04/23", [Preferred Date*]@row = "11/23/23", [Preferred Date*]@row = "11/24/23", [Preferred Date*]@row = "11/25/23", [Preferred Date*]@row = "11/26/23", [Preferred Date*]@row = "12/21/23", [Preferred Date*]@row = "12/22/23", [Preferred Date*]@row = "12/23/23", [Preferred Date*]@row = "12/24/23", [Preferred Date*]@row = "12/25/23", [Preferred Date*]@row = "12/26/23", [Preferred Date*]@row = "12/27/23", [Preferred Date*]@row = "12/28/23", [Preferred Date*]@row = "12/29/23", [Preferred Date*]@row = "12/30/23", [Preferred Date*]@row = "12/31/23", [Preferred Date*]@row = "01/01/24", [Preferred Date*]@row = "01/02/24", [Preferred Date*]@row = "01/03/24", [Preferred Date*]@row = "01/15/24", [Preferred Date*]@row = "02/19/24", [Preferred Date*]@row = "05/29/24", [Preferred Date*]@row = "07/04/24"), "University Holiday", IF(OR([Preferred Date*]@row < [First Date of AY24]#, [Preferred Date*]@row > [Last Date of AY24]#), "Date is not AY24", IF(AND([Does request/row include ILC spaces?*]@row = "Yes", OR([Preferred Date*]@row = "12/19/23", [Preferred Date*]@row = "12/20/23")), "Invalid ILC Date", "Valid Date")))
What am I missing? Do I need to indicate in that piece somehow that these are dates we're working with, since I'm using the "<" and ">" functions -- while all other pieces of the formula are referencing specific dates as text?
Thanks!
Mary
Best Answer
-
Thanks to Heather at the Pro Desk! Once the formula was re-ordered (moving the sheet summary reference to the front), the formula worked! Here's the final formula, for anyone interested:
=IF(ISBLANK([Preferred Date*]@row, " "), IF(OR([Preferred Date*]@row < [First Date of AY24]#, [Preferred Date*]@row > [Last Date of AY24]#), "Date is not AY24", IF(OR([Preferred Date*]@row = "09/04/23", [Preferred Date*]@row = "11/23/23", [Preferred Date*]@row = "11/24/23", [Preferred Date*]@row = "11/25/23", [Preferred Date*]@row = "11/26/23", [Preferred Date*]@row = "12/21/23", [Preferred Date*]@row = "12/22/23", [Preferred Date*]@row = "12/23/23", [Preferred Date*]@row = "12/24/23", [Preferred Date*]@row = "12/25/23", [Preferred Date*]@row = "12/26/23", [Preferred Date*]@row = "12/27/23", [Preferred Date*]@row = "12/28/23", [Preferred Date*]@row = "12/29/23", [Preferred Date*]@row = "12/30/23", [Preferred Date*]@row = "12/31/23", [Preferred Date*]@row = "01/01/24", [Preferred Date*]@row = "01/02/24", [Preferred Date*]@row = "01/03/24", [Preferred Date*]@row = "01/15/24", [Preferred Date*]@row = "02/19/24", [Preferred Date*]@row = "05/29/24", [Preferred Date*]@row = "07/04/24"), "University Holiday", IF(AND([Does request/row include ILC spaces?*]@row = "Yes", OR([Preferred Date*]@row = "12/19/23", [Preferred Date*]@row = "12/20/23")), "Invalid ILC Date", "Valid Date"))))
Answers
-
Is the [Preferred Date*] column set as a date type column or some other column type?
-
It is a date type column.
-
And are your Sheet Summary fields set as date type as well?
-
Yes, the sheet summary fields are date fields.
-
How are all of your dates being populated exactly?
-
I set the dates in the sheet summary fields: "First Date of AY24" and "Last Date of AY24". Users will fill in the "Preferred Date" on each row of their sheet. I've been using a formula for the University Holidays and Invalid ILC Dates for a few years. I tested in this year's template and the formula is still working for those fields. This year, I'm trying to add the function of notifying the user if their date is out of the AY24 range.
-
If the first part of the formula is working while referencing text strings, it leads me to believe that the dates in the [Preferred Date*] column are being stored as text strings and not actual dates. If that is the case, then it won't work when referencing actual dates.
But you say the field is set as a date type field. Are you able to provide some screenshots for reference? Could you enter a temporary checkbox type column and enter the following column formula then let me know how many boxes are in fact checked?
=IF(ISDATE([Preferred Date*]@row), 1)
-
What happens if you try:
=IF(ISDATE([First Date of AY24]#), 1)
and
=IF(ISDATE([Last Date of AY24]#), 1)
-
Thanks to Heather at the Pro Desk! Once the formula was re-ordered (moving the sheet summary reference to the front), the formula worked! Here's the final formula, for anyone interested:
=IF(ISBLANK([Preferred Date*]@row, " "), IF(OR([Preferred Date*]@row < [First Date of AY24]#, [Preferred Date*]@row > [Last Date of AY24]#), "Date is not AY24", IF(OR([Preferred Date*]@row = "09/04/23", [Preferred Date*]@row = "11/23/23", [Preferred Date*]@row = "11/24/23", [Preferred Date*]@row = "11/25/23", [Preferred Date*]@row = "11/26/23", [Preferred Date*]@row = "12/21/23", [Preferred Date*]@row = "12/22/23", [Preferred Date*]@row = "12/23/23", [Preferred Date*]@row = "12/24/23", [Preferred Date*]@row = "12/25/23", [Preferred Date*]@row = "12/26/23", [Preferred Date*]@row = "12/27/23", [Preferred Date*]@row = "12/28/23", [Preferred Date*]@row = "12/29/23", [Preferred Date*]@row = "12/30/23", [Preferred Date*]@row = "12/31/23", [Preferred Date*]@row = "01/01/24", [Preferred Date*]@row = "01/02/24", [Preferred Date*]@row = "01/03/24", [Preferred Date*]@row = "01/15/24", [Preferred Date*]@row = "02/19/24", [Preferred Date*]@row = "05/29/24", [Preferred Date*]@row = "07/04/24"), "University Holiday", IF(AND([Does request/row include ILC spaces?*]@row = "Yes", OR([Preferred Date*]@row = "12/19/23", [Preferred Date*]@row = "12/20/23")), "Invalid ILC Date", "Valid Date"))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!