Hi, I created a query, but Excel is not accepting it. Can you please help with what could be done to fix it?
=if(or(IF(AND(I9="Yes",I8="No"),DATEDIF(B9,C9,"d"),"NA"),if(and(i9="yes",i8="yes",B9=C8),DATEDIF(B9,C9,"d")-1,"NA")))
Hi Jayendra!
Your OR(IF) statements already have what to do if the statements are false. Either nix the first IF statement and have the formula read
=OR(IF(AND(I9="Yes",I8="No"),DATEDIF(B9,C9,"d"),"NA"),if(and(i9="yes",i8="yes",B9=C8),DATEDIF(B9,C9,"d")-1,"NA")).
or re-write it so that if neither of the OR statements are true the result would read NA
=if(or(IF(AND(I9="Yes",I8="No"),DATEDIF(B9,C9,"d")),if(and(i9="yes",i8="yes",B9=C8),DATEDIF(B9,C9,"d")-1)),,"NA").
If your looking for a TRUE/FALSE answer 86 the "NA" altogether. Not exactly sure what your desired result is so I hope one of these solutions gets you started on your solution.
Many thanks, Jessica. The first formula you suggested says True and the second NA. I am still not getting the desired number of days required. Following is the table for which I need result on the last column (Additional Allowance days). The purpose is to avoid counting one day more if a person is moving from one applicable 'Additional Allowance Station' to another (you see, the departure date of Place C and the arrival date of D are the same, and both fall under additional allowance station; so to avoid double counting 25/08/21. Thank you in advance for your support
| Place | ARRIVAL DATE | DEPARTURE DATE | Additional Allowance Station | Additional Allowance days |
| A | 07/08/21 | 07/08/21 | No | - |
| B | 07/08/21 | 24/08/21 | No | FALSE |
| C | 24/08/21 | 25/08/21 | Yes | NA |
| D | 25/08/21 | 10/10/21 | Yes | NA |
Can you please provide a sample worksheet with an example of the desired results. That way I'm not trying to recreate your spreadsheet.
Hello,
You can use following formula to get what you want.
=IF(AND(I8=I9,I9="Yes",B9=C8),(C9-B9)-1,
IF(AND(I8=I9;I9="No"),NA(),C9-B9))
Br,
Anders
Here, attached. Thanks.
Hi Anders,
Somehow it didn't work. Is it possible to do on the spreadsheet I shared?
Many thanks.
Jay
Hello,
See attached, if this is not the result you want then please give an example. I used the same formula as provided earlier.
Br,
Anders
Thanks a lot, Anders. It worked. I further added few more criteria so the result is as desired. I've attached the file here.
Best regards,
Jayendra
Hello,
Sorry, no file attached. If you’re having problem with getting correct results using AND / OR logic then perhaps this article can give you some help.
Br,
Anders
