Multiple IF, AND conditions to return True/False

[avenkat]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]MAL[/TD]
[TD]MQL[/TD]
[TD]SAL[/TD]
[TD]SQL[/TD]
[TD]First associated date[/TD]
[TD]Conditions True/False[/TD]
[/TR]
[TR]
[TD]Raw[/TD]
[TD]10/25/2016[/TD]
[TD]10/10/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/10/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pursuing[/TD]
[TD]1/12/2017[/TD]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl63"]4/18/2017[/TD]
[TD="class: xl63, width: 111"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4/18/2017[/TD]
[TD][/TD]
[TD]2/28/2018[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Status​

[/td][td]

MAL​

[/td][td]

MQL​

[/td][td]

SAL​

[/td][td]

SQL​

[/td][td]

First associated date​

[/td][td]

Conditions True/False​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Raw​

[/td][td]

10/25/2016​

[/td][td]

10/10/2016​

[/td][td][/td][td][/td][td]

10/10/2016​

[/td][td]

TRUE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Pursuing​

[/td][td]

01/12/2017​

[/td][td]

04/18/2017​

[/td][td]

04/18/2017​

[/td][td][/td][td]

02/28/2018​

[/td][td]

FALSE​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
[TABLE="width: 776"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]10/10/17 0:00[/TD]
[TD="align: right"]10/25/17 10:56[/TD]
[TD="align: right"]10/10/17 11:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: right"]11/14/17 0:00[/TD]
[TD="align: right"]11/16/17 7:24[/TD]
[TD="align: right"]11/14/17 10:51[/TD]
[TD="align: right"]11/16/17 7:24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/18 0:00[/TD]
[TD="align: right"]1/11/18 5:45[/TD]
[TD="align: right"]1/11/18 5:45[/TD]
[TD="align: right"]1/11/18 9:33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]11/22/17 0:00[/TD]
[TD="align: right"]11/27/17 11:55[/TD]
[TD="align: right"]11/22/17 16:33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]10/5/17 0:00[/TD]
[TD="align: right"]10/6/17 6:03[/TD]
[TD="align: right"]10/5/17 9:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

I am still getting a False, for conditions that look true.
My data looks like this
[TABLE="width: 776"]
<tbody>[TR]
[TD="align: right"]10/10/17 0:00[/TD]
[TD="align: right"]10/25/17 10:56[/TD]
[TD="align: right"]10/10/17 11:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="align: right"]11/14/17 0:00[/TD]
[TD="align: right"]11/16/17 7:24[/TD]
[TD="align: right"]11/14/17 10:51[/TD]
[TD="align: right"]11/16/17 7:24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/18 0:00[/TD]
[TD="align: right"]1/11/18 5:45[/TD]
[TD="align: right"]1/11/18 5:45[/TD]
[TD="align: right"]1/11/18 9:33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]11/22/17 0:00[/TD]
[TD="align: right"]11/27/17 11:55[/TD]
[TD="align: right"]11/22/17 16:33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD="align: right"]10/5/17 0:00[/TD]
[TD="align: right"]10/6/17 6:03[/TD]
[TD="align: right"]10/5/17 9:59[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

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 !.
[TABLE="width: 878"]
<tbody>[TR]
[TD]Contact Status[/TD]
[TD]Member First Associated Date[/TD]
[TD]Timestamp Lifecycle MAL[/TD]
[TD]Timestamp Lifecycle MQL[/TD]
[TD]Timestamp Lifecycle SAL[/TD]
[TD]Timestamp Lifecycle SQL[/TD]
[TD]Timestamp Lifecycle SAO[/TD]
[TD]Timestamp Lifecycle SQO[/TD]
[TD]MAL Stage TRUE[/TD]
[/TR]
[TR]
[TD]Rejected[/TD]
[TD="align: right"]10/10/17 0:00[/TD]
[TD="align: right"]10/25/17 10:56[/TD]
[TD="align: right"]10/10/17 11:05[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unqualified[/TD]
[TD="align: right"]11/14/17 0:00[/TD]
[TD="align: right"]11/16/17 7:24[/TD]
[TD="align: right"]11/14/17 10:51[/TD]
[TD="align: right"]11/16/17 7:24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]Pursuing[/TD]
[TD="align: right"]1/11/18 0:00[/TD]
[TD="align: right"]1/11/18 5:45[/TD]
[TD="align: right"]1/11/18 5:45[/TD]
[TD="align: right"]1/11/18 9:33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]Rejected[/TD]
[TD="align: right"]11/22/17 0:00[/TD]
[TD="align: right"]11/27/17 11:55[/TD]
[TD="align: right"]11/22/17 16:33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]


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"})))