Date validation formula referencing Sheet Summary fields

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 ✓

    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 ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mary Ayers
    Mary Ayers ✭✭✭✭

    It is a date type column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mary Ayers
    Mary Ayers ✭✭✭✭

    Yes, the sheet summary fields are date fields.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mary Ayers
    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
    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)

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Mary Ayers
    Mary Ayers ✭✭✭✭
    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"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!