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)

Mary Ayers

Thank you for the advice on the iferror/list. The second formula does work on its own. The problem I'm having is trying to combine these two formulas into one.

L_123

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

 

If they both work just replace the "" at the end of the first formula with the second formula minus the = sign. Only way this would have an issue is either there is an issue with one of the formulas, or it reaches the length limit of smartsheet formulas.

Mary Ayers

Thanks for the help! I finally got it to work -- after finding and eliminating the curly quote!

kelly906

What a GREAT way to state this... "If they both work just replace the "" at the end of the first formula with the second formula minus the = sign. Only way this would have an issue is either there is an issue with one of the formulas, or it reaches the length limit of smartsheet formulas"

Was so easy to figure out once I read your comment. Provided another example below in case it helps anyone else. Thanks all these years later!

Built two working statements...

=IF(AND([COVID Project Status]@row <> "Active", [SW Border Project Status]@row <> "Active"), "Inactive")

=IF(OR([COVID Project Status]@row = "Active", [SW Border Project Status]@row = "Active"), "Active")

...and combined them into one per 'L_123' recommendation...

=IF(AND([COVID Project Status]@row <> "Active", [SW Border Project Status]@row <> "Active"), "Inactive", IF(OR([COVID Project Status]@row = "Active", [SW Border Project Status]@row = "Active"), "Active"))