Multiple IF, AND conditions to return True/False

[avenkat]

Status	MAL	MQL	SAL	SQL	First associated date	Conditions True/False
Raw	10/25/2016	10/10/2016			10/10/2016
Pursuing	1/12/2017	4/18/2017 <tbody> </tbody>	4/18/2017		2/28/2018

<tbody>
</tbody>

So these being my columns, I need a To fill the last column with conditions that satisfy

1) IF (AND(MQL<=MAL,MQl=Firstassocaited date),OR(Status="Raw","Rejected","Unqualified"),TRUE,FALSE)
For some reason this formula return with a a FALSE for everything (see first data set where the conditions are met!)
2) IF(AND(SAL<=MQL,SAL<=Firstassocaited date, MQL>MAL,Status=Pursuing),TRUE,FALSE)

Not sure if this formula makes sense .
I need to know if the conditions satisfy via dates and status is reflecting the process is followed then true else false.


Thanks so much for any pointers

 

[cerfani]

dates are just decimal values so yes you can test dates with comparison operators like <, >, =, <> ... etc

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​
1​	Status​	MAL​	MQL​	SAL​	SQL​	First associated date​	Conditions True/False​
2​	Raw​	10/25/2016​	10/10/2016​			10/10/2016​	TRUE​
3​	Pursuing​	01/12/2017​	04/18/2017​	04/18/2017​		02/28/2018​	FALSE​
4​

Formula in G2 copied down
=IF(AND(AND(C2<=B2,C2=F2),OR(A2={"Raw";"Rejected";"Unqualified"})),TRUE,FALSE)

M.

 

[jtakw]

Hi,

If I understand correctly, try this:

Book1
	A	B	C	D	E	F	G	H
1	Status	MAL	MQL		SAL	SQL	First associated date	Conditions True/False
2	Raw	10/25/2016	10/10/2016				10/10/2016	TRUE
3	Pursuing	1/12/2017	4/18/2017		4/18/2017		2/28/2018	TRUE
Sheet72

Cell Formulas
Range	Formula
H2	=OR(AND(C2<=B2,C2=G2,OR(A2={"Raw","Rejected","Unqualified"})),AND(E2<=C2,E2<=G2,C2>B2,A2="Pursuing"))

H2 formula copied down.

 

[avenkat]

I am still getting a False, for conditions that look true.
My data looks like this

10/10/17 0:00	10/25/17 10:56	10/10/17 11:05					#REF!
11/14/17 0:00	11/16/17 7:24	11/14/17 10:51	11/16/17 7:24				FALSE
1/11/18 0:00	1/11/18 5:45	1/11/18 5:45	1/11/18 9:33				FALSE
11/22/17 0:00	11/27/17 11:55	11/22/17 16:33					FALSE
10/5/17 0:00	10/6/17 6:03	10/5/17 9:59					FALSE

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

I am still getting a False, for conditions that look true.
My data looks like this

10/10/17 0:00	10/25/17 10:56	10/10/17 11:05					#REF!
11/14/17 0:00	11/16/17 7:24	11/14/17 10:51	11/16/17 7:24				FALSE
1/11/18 0:00	1/11/18 5:45	1/11/18 5:45	1/11/18 9:33				FALSE
11/22/17 0:00	11/27/17 11:55	11/22/17 16:33					FALSE
10/5/17 0:00	10/6/17 6:03	10/5/17 9:59					FALSE

<tbody>
</tbody>

This data sample doesn't correspond to the data sample in your original post.
Could you provide a better sample along with expected results?

M.

 

[avenkat]

Sorry if it was unclear !.

Contact Status	Member First Associated Date	Timestamp Lifecycle MAL	Timestamp Lifecycle MQL	Timestamp Lifecycle SAL	Timestamp Lifecycle SQL	Timestamp Lifecycle SAO	Timestamp Lifecycle SQO	MAL Stage TRUE
Rejected	10/10/17 0:00	10/25/17 10:56	10/10/17 11:05
Unqualified	11/14/17 0:00	11/16/17 7:24	11/14/17 10:51	11/16/17 7:24				FALSE
Pursuing	1/11/18 0:00	1/11/18 5:45	1/11/18 5:45	1/11/18 9:33				FALSE
Rejected	11/22/17 0:00	11/27/17 11:55	11/22/17 16:33					FALSE

<tbody>
</tbody>

I need to prove that a row is following the process of what status it is in.
If status is Rejected/raw/Unqualified then the conditions satisfied should be MQL<=MAL, MQL=First responded date. If this is true then TRUE else FALSE
If status is Pursuing/Restored/New then the conditions satisfied should be SAL<=MQL, MQL>MAL,MQL=first responded date . IF true then TRUE else FALSE

Currently when I do MQL<MAL , it comes to true but when I add/do (MQL=First responded date) it comes to False even if the dates are same,.
I changed the date format to without time but still i get a False

 

[jtakw]

Hi,

Your description and setup has changed in Post #7 compared to OP, Also, I assume the TIME part of the Time Stamps is irrelevant and you're Only looking at the Date:

Book1
	A	B	C	D	E	F	G	H	I
1	Contact Status	Member First Associated Date	Timestamp Lifecycle MAL	Timestamp Lifecycle MQL	Timestamp Lifecycle SAL	Timestamp Lifecycle SQL	Timestamp Lifecycle SAO	Timestamp Lifecycle SQO	MAL Stage
2	Rejected	10/10/2017 0:00	10/25/2017 10:56	10/10/2017 11:05					TRUE
3	Unqualified	11/14/2017 0:00	11/16/2017 7:24	11/14/2017 10:51	11/16/2017 7:24				TRUE
4	Pursuing	1/11/2018 0:00	1/11/2018 5:45	1/11/2018 5:45	1/11/2018 9:33				FALSE
5	Rejected	11/22/2017 0:00	11/27/2017 11:55	11/22/2017 16:33					TRUE
Sheet72

Cell Formulas
Range	Formula
I2	=OR(AND(INT(VALUE(D2))<=INT(VALUE(C2)),INT(VALUE(D2))=INT(VALUE(B2)),OR(A2={"Rejected","Raw","Unqualified"})),AND(INT(VALUE(E2))<=INT(VALUE(D2)),INT(VALUE(D2))>INT(VALUE(C2)),INT(VALUE(D2))=INT(VALUE(B2)),OR(A2={"Pursuing","Restored","New"})))

Formula copied down.

 

[avenkat]

Thank you Marcelo, Jtakw.

I had intended it to put some proxy value in the OP. On retrospect it looks like i should have just copied a part of my data in OP.

Thank you for the output. This works but I still get an error in D2=B2 thus making my formula output as False.

I am going to try using INT and lets see how that works.

 

[jtakw]

You're welcome.

I must have had a brain freeze earlier, since I'm using INT in my formula, VALUE was Not needed, updated formula here:

Book1
	A	B	C	D	E	F	G	H	I
1	Contact Status	Member First Associated Date	Timestamp Lifecycle MAL	Timestamp Lifecycle MQL	Timestamp Lifecycle SAL	Timestamp Lifecycle SQL	Timestamp Lifecycle SAO	Timestamp Lifecycle SQO	MAL Stage
2	Rejected	10/10/2017 0:00	10/25/2017 10:56	10/10/2017 11:05					TRUE
3	Unqualified	11/14/2017 0:00	11/16/2017 7:24	11/14/2017 10:51	11/16/2017 7:24				TRUE
4	Pursuing	1/11/2018 0:00	1/11/2018 5:45	1/11/2018 5:45	1/11/2018 9:33				FALSE
5	Rejected	11/22/2017 0:00	11/27/2017 11:55	11/22/2017 16:33					TRUE
Sheet72

Cell Formulas
Range	Formula
I2	=OR(AND(INT(D2)<=INT(C2),INT(D2)=INT(B2),OR(A2={"Rejected","Raw","Unqualified"})),AND(INT(E2)<=INT(D2),INT(D2)>INT(C2),INT(D2)=INT(B2),OR(A2={"Pursuing","Restored","New"})))