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​

 

[Joe4]

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

Hey Jeffrey,
Since AND is already a Boolean function, you can simplify that formula to just this:

=AND(B3<=G3,C3<=G3,D3<>"F",D3<>"I",C3>0)

 

[Jeffrey Mahoney]

Hey Jeffrey,
Since AND is already a Boolean function, you can simplify that formula to just this:

=AND(B3<=G3,C3<=G3,D3<>"F",D3<>"I",C3>0)

True. Pun intended. ?

 

[ItalianPlatinum]

None of those formulas work do they work for any of you? This is what i see

 

[Jeffrey Mahoney]

As far as they way you described the logic should work, My formula has given good values.

In your last example in column H and rows 4 and 5 you have TRUE responses. With zeros in the DATE2 column and the letter I in the Indicator column, I don't see how that could be. I also don't know this could work if you have no dates in column I. Are you expecting the formula to look at the single date at the column 7? IF that is the case then the only row to show a TRUE response would be Row 8.

 

[ItalianPlatinum]

I have a data set of 4,000 rows that auto populates to me, Indicator I has no date 2 and populates as zero (0). so the data that comes to me cannot be adjusted. So the goal is to use a formula in an adjacent column for a formula with True/False for a filter (show me any true) seeing I want to filter out this:

1. F indicator dont want to see
2. I indicator Date 1 on BD2 or greater dont want to see
3. A indicator Date 1 on BD2 or greater dont want to see
4. A indicator Date 2 on BD2 or greater dont want to see

unfortunately i am testing all formulas provided and they don't seem to yield the results. are you able to show me a file where you are seeing them match using your formula which in my above is (formula 1)? unless me transposing the formula is the issue seeing it is working for you and somehow not for me. all values are in "general" format

 

[Jeffrey Mahoney]

Let me get this straight:
* TRUE results will get hidden?
* F Indicator should be TRUE?
* I Indicator should be TRUE?
* A Indicator with either Date 1 or Date 2 is equal to or greater than Cell BD2 should be TRUE?

 

[ItalianPlatinum]

no you have it backwards. TRUE meaning condition is tripped so i want to see it. honestly doesn't matter either way really just need true or false to properly show.

(treated as hidden)
F indicator always false
I indicator only false if date 1 is greater or equal to BD2 minus 1 (ignore date 2 in any logic)
A indicator only false if both dates are greater or equal to BD2 minus 1

 

[Jeffrey Mahoney]

Either the logic keeps changing or I don't have enough patience to keep trying. I am respectfully bowing out.

 

[ItalianPlatinum]

My table never changed (example table) so I unfortunately disagree with "it keeps changing." I showed all formulas provided the results on multiple occasions. You have yet to share an example showing those provided formulas worked for you, just implying they work. @kweaver formula was the closest other than 2 records. I can copy here again the requirements but they have been the same the whole time the only typo i had was the one kweaver identified and agree i had the wrong signage and that is where i clarified in the entirety in post 9.

 

[ItalianPlatinum]

Just to close the loop I was able to use a similar formula provided to me on a prior unrelated thread from @jtakw one thing I noticed and will need to find another way to solution. It only works if i have the date reference in number format (20220502). so had to create a workaround for the date formula using =TEXT(WORKDAY(EOMONTH(TODAY(),-1),2,[HOLIDAYS.xlsx]HOLIDAYS!$A$1:$A$1000)-1,"YYYYMMDD")*1

=NOT(AND(B2>=$J$2,IF(D2<>"I",C2>=$J$2,1)))