Combining IF(AND and IF(OR statements

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
Comments
-
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 ✭✭✭
这个国家的好办法…“如果他们两个卧室rk 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"))
Help Article Resources
Categories
=IF(AND([Build Approved]@row = 1, [Install Complete]@row = 1, [In Production]@row = 1), \"In Production\", IF(AND([Build Approved]@row = 1, [Install Complete]@row = 1, [In Production]@row = 0), \"Installed\", IF(AND([Build Approved]@row = 1, [Install Complete]@row = 0, [In Production]@row = 0), \"Build Approved\", \"Design\")))<\/p>"},{"commentID":379709,"body":"
=IF([Build Approved]@row = 0, \"Design\", IF([Install Complete]@row = 0, \"Build Approved\", IF([In Production]@row = 0, \"Installed\", \"In Production\")))<\/p>
This may work.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106232,"type":"question","name":"Check if project start\/end dates fall within specific time frame.","excerpt":"My team works on projects that typically take 6-12 months. I'd like to create a roll-up sheet that identifies which quarters in which a project was worked on. I'm creating a new row for each project and linking in the start and end dates from each of the projects' sheets. Then I've created a new column for each quarter…","categoryID":322,"dateInserted":"2023-06-08T19:44:47+00:00","dateUpdated":"2023-06-08T22:03:24+00:00","dateLastComment":"2023-06-08T20:08:51+00:00","insertUserID":161563,"insertUser":{"userID":161563,"name":"gunnell","title":"Director, eLearning Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/gunnell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":161563,"lastUserID":161563,"lastUser":{"userID":161563,"name":"gunnell","title":"Director, eLearning Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/gunnell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":23,"score":null,"hot":3372509018,"url":"https:\/\/community.smartsheet.com\/discussion\/106232\/check-if-project-start-end-dates-fall-within-specific-time-frame","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106232\/check-if-project-start-end-dates-fall-within-specific-time-frame","format":"Rich","lastPost":{"discussionID":106232,"commentID":379713,"name":"Re: Check if project start\/end dates fall within specific time frame.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379713#Comment_379713","dateInserted":"2023-06-08T20:08:51+00:00","insertUserID":161563,"insertUser":{"userID":161563,"name":"gunnell","title":"Director, eLearning Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/gunnell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-08T20:09:01+00:00","dateAnswered":"2023-06-08T20:08:51+00:00","acceptedAnswers":[{"commentID":379713,"body":"
<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106219,"type":"question","name":"Metrics NOT updating periodically as expected","excerpt":"Not sure if anyone is experiencing this. I've created a METRICS sheet for every project that sums up counts for Task Health in order to present on Dashboard through Graphs. I'm noticing that theses metrics are NOT automatically updated unless I refresh the page a dozen times OR re-write the exact same formula again. But…","categoryID":322,"dateInserted":"2023-06-08T17:33:54+00:00","dateUpdated":null,"dateLastComment":"2023-06-08T21:33:47+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T21:32:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161931,"lastUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T21:32:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":35,"score":null,"hot":3372508061,"url":"https:\/\/community.smartsheet.com\/discussion\/106219\/metrics-not-updating-periodically-as-expected","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106219\/metrics-not-updating-periodically-as-expected","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106219,"commentID":379733,"name":"Re: Metrics NOT updating periodically as expected","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379733#Comment_379733","dateInserted":"2023-06-08T21:33:47+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-08T21:32:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/FUCVWKMTVZVX\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-08T21:32:09+00:00","dateAnswered":"2023-06-08T21:03:22+00:00","acceptedAnswers":[{"commentID":379727,"body":"