HELP with this if formula please

[kkoc3karan]

I am trying to find all record which are Not on leave!

attached an excel sheet sheet 4 is the one

in column M i am using an if statement which checks if

leave start date <= a date value and leave end date >= a date value
this gives me all the people who are on leave for the dates specified

i need reverse of this ( dont want to change the if true or if false parameters )
i want reverse of this logic
leave start date <= a date value and leave end date >= a date value
Please help !!!!

 

[NARAYANK991]

Hi Karan ,

The logic you are using is as follows :

If the Leave Start Date is before the Given From Date ( 8/11/2014 ) , and the Leave End Date is after the Given To Date ( 8/15/2014 ) , then Leave Taken is YES , else it is NO.

If the Leave Start Date is before the Given From Date ( 8/11/2014 ) , but the Leave End Date is before the Given To Date ( 8/15/2014 ) , what should be the status of Leave Taken ?

The correct logic for this would be :

If the Leave Start Date is before the Given To Date ( 8/15/2014 ) , and the Leave End Date is after the Given From Date ( 8/11/2014 ) , then Leave Taken is YES , else it is NO.

Narayan

 

[kkoc3karan]

Hi Karan ,

The logic you are using is as follows :

If the Leave Start Date is before the Given From Date ( 8/11/2014 ) , and the Leave End Date is after the Given To Date ( 8/15/2014 ) , then Leave Taken is YES , else it is NO.

If the Leave Start Date is before the Given From Date ( 8/11/2014 ) , but the Leave End Date is before the Given To Date ( 8/15/2014 ) , what should be the status of Leave Taken ?

The correct logic for this would be :

If the Leave Start Date is before the Given To Date ( 8/15/2014 ) , and the Leave End Date is after the Given From Date ( 8/11/2014 ) , then Leave Taken is YES , else it is NO.

Narayan

Hi Narayan

Thanks for responding

What i am trying to find out is all the employees who are available for the entire period which is

from 11/aug/2014
To 15/aug/2014

if i use your logic then i will get records who might be on leave for few days during the 2 dates

thanks

 

[NARAYANK991]

Hi Karan ,

I understand that if you want to know whether an employee was available for the whole period between two dates , your logic is correct.

I was talking about the logic which decides whether an employee is on leave.

If you wish to know whether an employee was available during the entire period , you can use the following OR logic :

=IF(OR(Leave End Date < Given from Date , Leave Start Date > Given To Date) , "Available" , "Not Available")

Narayan

 

[kkoc3karan]

Hi Karan ,

I understand that if you want to know whether an employee was available for the whole period between two dates , your logic is correct.

I was talking about the logic which decides whether an employee is on leave.

If you wish to know whether an employee was available during the entire period , you can use the following OR logic :

=IF(OR(Leave End Date < Given from Date , Leave Start Date > Given To Date) , "Available" , "Not Available")

Narayan

OMG that totally worked !!!!. I have spent more than 6HRS figuring this out by myself .

Thank you so much for helping Narayan . I really appreciate it .

I am new to all this , Any suggestions on how i can improve and stuff . Reading doing etc .

thanks heaps mate