Log In
Register
Home
Active Member
December 3, 2021
Offline
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")))
Trusted Members
February 13, 2021
Offline
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.
Active Member
December 3, 2021
Offline
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 |
Trusted Members
February 13, 2021
Offline
Can you please provide a sample worksheet with an example of the desired results. That way I'm not trying to recreate your spreadsheet.
VIP
Trusted Members
December 7, 2016
Offline
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
Active Member
December 3, 2021
Offline
Here, attached. Thanks.
Active Member
December 3, 2021
Offline
Hi Anders,
Somehow it didn't work. Is it possible to do on the spreadsheet I shared?
Many thanks.
Jay
VIP
Trusted Members
December 7, 2016
Offline
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
Active Member
December 3, 2021
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
1 Guest(s)
