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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It is a date type column.
-
And are your Sheet Summary fields set as date type as well?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes, the sheet summary fields are date fields.
-
How are all of your dates being populated exactly?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
What happens if you try:
=IF(ISDATE([First Date of AY24]#), 1)
and
=IF(ISDATE([Last Date of AY24]#), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!