Formula needed for true false multiple conditions

[ItalianPlatinum]

Hello this is similar to a prior post I made but different so creating a new thread. Looking for a formula to give me a true or false statement as outlined below, need it to check if Date 1 and Date 2 are both < or equal to column 7 (TRUE) with exceptions where needs to ignore indicator F and indicator I date 2 populates as zero so another ignore. Date 1 and Date 2 are in general format if that matters

Name	Date 1	Date 2	Indicator	FORMULA NEEDED	Expected to see	2nd Business DAY minus 1
AAA	20220503​	20220503​	F		FALSE​	20220502
BBB	20220503​	20220503​	F		FALSE​
CCC	20220428​	0​	I		TRUE​
DDD	20220428​	0​	I		TRUE​
EEE	20220503​	0​	I		FALSE​
FFF	20220428​	20220428​	A		TRUE​
GGG	20220428​	20220503​	A		TRUE​
HHH	20220503​	20220503​	A		FALSE​
III	20220503​	20220428​	A		TRUE​

 

[Jeffrey Mahoney]

How about this in first row column 6
=IF(AND(B3<=G3,C3<=G3,D3<>"F",D3<>"I",C3>0),TRUE,FALSE)

 

[kweaver]

I think some of your results in Expected are incorrect.

Is this correct:

temporary (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Name	Date 1	Date 2	Indicator	FORMULA NEEDED	Expected to see	2nd Business DAY minus 1	Alternative
2	AAA	20220503	20220503	F		FALSE	20220502	FALSE
3	BBB	20220503	20220503	F		FALSE		FALSE
4	CCC	20220428	0	I		TRUE		TRUE
5	DDD	20220428	0	I		TRUE		TRUE
6	EEE	20220503	0	I		FALSE		FALSE
7	FFF	20220428	20220428	A		TRUE		TRUE
8	GGG	20220428	20220503	A		TRUE		FALSE
9	HHH	20220503	20220503	A		FALSE		FALSE
10	III	20220502	20220428	A		TRUE		TRUE
Sheet2

Cell Formulas
Range	Formula
H2:H10	H2	=IF(OR(C2=0,D2="F",D2="I"),IF(B2<=$G$2,TRUE,FALSE),IF(AND(B2<=$G$2,C2<=$G$2),TRUE, FALSE))

 

[ItalianPlatinum]

my expected results are correct. if both date 1 and date 2 for indicator A are >= 5/2 then False, if indicator I Date 1 is >= 5/2 then false. if anything else then its true. The date in the 2nd BD minus 1 field is in custom format the formula is this? so when I use your formula everything shows as true.

In your picture name GGG should be true because both dates arent greater or equal to 5/3

here is the formula for the BD2 minus 1
=TEXT(WORKDAY(EOMONTH(TODAY(),-1),2,X/x/x[HOLIDAYS.xlsx]HOLIDAYS'!$A$1:$A$1000)-1,"YYYYMMDD")

 

[kweaver]

Didn't your original OP say <= ?? -- "Date 1 and Date 2 are both < or equal to column 7 (TRUE)"

 

[ItalianPlatinum]

yes you are correct my sign in post 4 is backwards. either way my table still holds. the expected column is correct

 

[kweaver]

Maybe you could restate ALL of the conditions correctly and we'll start again.

 

[Jeffrey Mahoney]

Seems to me that we've provided the mechanism for the solution, all the poster needs to do is change the logic to suit there needs. I suggest that they try and make it work and come back if they are still having issues.

 

[ItalianPlatinum]

neither formula works. see below:

Name	Date 1	Date 2	Indicator	FORMULA 1	FORMULA 2	Expected to see	2nd Business DAY minus 1
AAA	20220503​	20220503​	F	FALSE​	TRUE​	FALSE​	20220502
BBB	20220503​	20220503​	F	FALSE​	TRUE​	FALSE​
CCC	20220428​	0​	I	FALSE​	TRUE​	TRUE​
DDD	20220428​	0​	I	FALSE​	TRUE​	TRUE​
EEE	20220503​	0​	I	FALSE​	TRUE​	FALSE​
FFF	20220428​	20220428​	A	FALSE​	TRUE​	TRUE​
GGG	20220428​	20220503​	A	FALSE​	TRUE​	TRUE​
HHH	20220503​	20220503​	A	FALSE​	TRUE​	FALSE​
III	20220503​	20220428​	A	FALSE​	TRUE​	TRUE​

Formula 1: =IF(AND(B2<=H2,C2<=H2,D2<>"F",D2<>"I",C2>0),TRUE,FALSE)
Formula 2: =IF(OR(C2=0,D2="F",D2="I"),IF(B2<=$H$2,TRUE,FALSE),IF(AND(B2<=$H$2,C2<=$H$2),TRUE, FALSE))
2nd BD minus 1: =TEXT(WORKDAY(EOMONTH(TODAY(),-1),2,[HOLIDAYS.xlsx]HOLIDAYS!$A$1:$A$1000)-1,"YYYYMMDD")

clarifying the ask seeing there is confusion, reference the expected to see column as well

1. indicator I and A in scope
2. indicator F out of scope
3. Date 1 and Date 2 if both are equal or less than 2nd BD minus 1 then (true)
4. indicator I will not have a date 2, so only check Date 1 for indication I

I think the formula for date being in TEXT may be causing some issue

 

[ItalianPlatinum]

if i paste values and convert the cell to number format this is what i get

Name	Date 1	Date 2	Indicator	FORMULA 1	FORMULA 2	Expected to see	2nd Business DAY minus 1
AAA	20220503​	20220503​	F	FALSE​	FALSE​	FALSE​	20220502
BBB	20220503​	20220503​	F	FALSE​	FALSE​	FALSE​
CCC	20220428​	0​	I	FALSE​	TRUE​	TRUE​
DDD	20220428​	0​	I	FALSE​	TRUE​	TRUE​
EEE	20220503​	0​	I	FALSE​	FALSE​	FALSE​
FFF	20220428​	20220428​	A	FALSE​	TRUE​	TRUE​
GGG	20220428​	20220503​	A	FALSE​	FALSE​	TRUE​
HHH	20220503​	20220503​	A	FALSE​	FALSE​	FALSE​
III	20220503​	20220428​	A	FALSE​	FALSE​	TRUE​