Date validation formula referencing Sheet Summary fields

Mary Ayers

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

Mary Ayers

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"))))

Paul Newcome

Is the [Preferred Date*] column set as a date type column or some other column type?

Mary Ayers

It is a date type column.

Paul Newcome

And are your Sheet Summary fields set as date type as well?

Mary Ayers

Yes, the sheet summary fields are date fields.

Paul Newcome

How are all of your dates being populated exactly?

Mary Ayers

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.

Paul Newcome

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)

Mary Ayers

Paul Newcome

What happens if you try:

=IF(ISDATE([First Date of AY24]#), 1)

and

=IF(ISDATE([Last Date of AY24]#), 1)

Mary Ayers

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"))))