Forum Discussion
Solved
IF AND OR
Hi everyone,
Could someone pls help me on using IF AND OR functions. I wanted do a kinda XLoopup but more of a IF with multiple criteria. I have a leave taken and ended in a sheet called COMP Leave and table named "COMP_Leave". In another sheet I have an employee ID and date range from 3 July 2023 till 20 July 2023. In the orange cell i want to put a formula saying that if an employee took leave on 3/7/23 If thats true or if 3/7/2023 falls between the date range given in "COMP_Leave" then I want the answer to be "Leave" else 5. And later i want to drag the cell all the way to 20/7/23.
The thing is the date data in the "COMP_Leave" table is generated from power query linked to my system, so that everytime i hit refresh I get the answers in Sheet2 automatically updated. Not very sure how to deal with Column [LeaveStartDate] and [LeaveEndDate].
BenjoBenjo - Not sure why it's not working for you. I copied your final formula and it works for me.
Regarding --, we must use --. It's simple. First hyphen(-) negates the TRUE and the second hyphen reverse the negation(Negative multiplied by Negative is a positive number).
SanthoshKunder I am trying to execute something like this:
IF(AND(FTEww[@[Employee ID]:[Employee ID]] ="E0144",
OR(G$7<=COMP_Leave[[LeaveStartDate]:[LeaveStartDate]],G$7>=COMP_Leave[[LeaveEndDate]:[LeaveEndDate]])),
"LWoP",5)SanthoshKunder This is awesome!!! But I want to refer to the table named "COMP_Leave" from different sheet. My date range is not static and will change when i refresh. SO I am looking forward to reference table instead of static dates. Thanks
