Combining IF(AND and IF(OR statements

Mary Ayers

Hi all,

I am trying to build a combined IF statement for a date validation field. Both of these statements work on their own.

The first statement returns "University Holiday" if the "Preferred Date" is a holiday...

=IF(OR([Preferred Date*]1 = "09/02/19", [Preferred Date*]1 = "11/28/19", [Preferred Date*]1 = "11/29/19", [Preferred Date*]1 = "11/30/19", [Preferred Date*]1 = "12/01/19", [Preferred Date*]1 = "12/23/19", [Preferred Date*]1 = "12/24/19", [Preferred Date*]1 = "12/25/19", [Preferred Date*]1 = "12/26/19", [Preferred Date*]1 = "12/27/19", [Preferred Date*]1 = "12/28/19", [Preferred Date*]1 = "12/29/19", [Preferred Date*]1 = "12/30/19", [Preferred Date*]1 = "12/31/19", [Preferred Date*]1 = "01/01/20", [Preferred Date*]1 = "01/02/20", [Preferred Date*]1 = "01/03/20", [Preferred Date*]1 = "01/04/20", [Preferred Date*]1 = "01/05/20", [Preferred Date*]1 = "01/20/20", [Preferred Date*]1 = "02/17/20", [Preferred Date*]1 = "05/25/20", [Preferred Date*]1 = "07/03/20", [Preferred Date*]1 = "07/04/20"), "University Holiday", "")

The second statement returns "Invalid ILC Date" if the "Does request/row include ILC spaces?" field is "Yes" and the "Preferred Date" is one of the three dates in this statement

=IF(AND([Does request/row include ILC spaces?*]1 = "Yes", OR([Preferred Date*]1 = "12/19/19", [Preferred Date*]1 = "12/20/19", [Preferred Date*]1 = "01/06/20")), “Invalid ILC Date", "")

Thanks in advance for any suggestions!!

Mary

L_123

instead of such a long if statement I would create a column off to the side to contain the list of dates, then use a match to check.

the formula would be something like

=iferror(match([Preferred Date*]1,[Holiday List]:[Holiday List],0),"University Holiday")

This will save you a lot of time setting up formulas each year as the sheet is used. If you want you can even play around with the Date() formula to make it autoupdate itself every year (except for easter   ) 

 

your issue in the second formula is that you are referencing a date column with text. "01/01/19" does not refer to a date. It refers to a set of text that we see as a date. Instead you need to use the Date formula. Date(19,1,1)