Date validation formula referencing Sheet Summary fields

Options
Mary Ayers
Mary Ayers ✭✭✭✭
edited 03/08/23 in Formulas and Functions

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

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    Answer ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    Options

    It is a date type column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    Options

    Yes, the sheet summary fields are date fields.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    How are all of your dates being populated exactly?

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 03/10/23
    Options

    What happens if you try:

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

    and

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

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!