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.
BenjoBenjo Can you please attach a sample workbook with your data( please mask your confidential data with some random texts)
SanthoshKunderAwesome!! this looks perfect. I will have to check the dynamic array by changing the leave dates. Hopefully it works. By the way could you kindly tell me what does that -- sign do? Thanks Santosh.
- It converts TRUE, FALSE into 1, 0
--TRUE=1