Forum Discussion
IF AND OR
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 Your formula works fine. But my data got other employees as well and when i apply to every cell and drag, it does not work,infact the leave "LWoP"applies to every employee from 10/7/23-31/7/23 which is incorrect. I just wanted this to effect employee E0144. I was wondering if we can use MAX and MIN with dates?something like this:
=IF(SUM(--(D$4>=MIN(COMP_Leave[[LeaveStartDate]:[LeaveStartDate]]))*(D$4<=MAX(COMP_Leave[[LeaveEndDate]:[LeaveEndDate]]))*(COMP_Leave[[EntitlementType]:[EntitlementType]]="UNPAID LEAVE")*(COMP_Leave[[EmployeeID]:[EmployeeID]]="E0144"))=1,"LWWWW",7.6)
Your help would be much appreciated.
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
