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)
SanthoshKunder Hi, I used the formul as you suggested ad worked fine but Its also picking Unpaid leave on 20, 26,27 and 28 Sept. These dates are unpaid leave for E0177. Looks like condition of UNPAID LEAVE is working fine but seems like condition of Employee number is not working. Your help would be appreciated. Thank you.
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=1SanthoshKunder Hi Santosh, Your formula was a great help but the text "LWoP" applies to all the employees from the date 10 July till 31 July. Seems like its not picking particular employee "E0144". Would really appreciate your help. My final formula is
IF(SUM(--(L$7>=COMP_Leave[[LeaveStartDate]:[LeaveStartDate]])*(L$7<=COMP_Leave[[LeaveEndDate]:[LeaveEndDate]])*(COMP_Leave[[EntitlementType]:[EntitlementType]]="UNPAID LEAVE")*(COMP_Leave[[EmployeeID]:[EmployeeID]]="E0144"))=1,"LWoP",5)
Also, does a number of -- makes a difference? When should we put - and when to put --
