An excel formula needed to determine T/F on dates

[ItalianPlatinum]

Hello,
I need some help trying to create a formula that will identify if the date1 and date2 don't equal next BD -2 (i have that formula in a cell to reference).

There are a few variables though:

1. indicator "I" will have the date 2 as 0
2. indicator I and A date 1 and date 2 will be on a last BD of prior month -1 date on every BD 1 (this is ok). (i also have that formula in a cell to reference)

I know a lot going on. ideally would like 1 formula unless i need multiple helper columns to bring it together?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Account	Indicator	Date 1	Date 2	Formula (HELP)	Desired Result		Next BD -2	20230731	LAST BD PM-1	20230730
2	1	F	20230731	20230731		FALSE
3	2	F	20230731	20230731		FALSE
4	3	F	20230731	20230731		FALSE
5	4	F	20230731	20230731		FALSE
6	5	F	20230731	20230731		FALSE
7	6	F	20230731	20230731		FALSE
8	7	F	20230730	20230730		TRUE	TEST
9	8	F	20230730	20230731		TRUE	TEST
10	9	F	20230731	20230730		TRUE	TEST
11	1	I	20230731	0		TRUE	TEST
12	2	I	20230729	0		TRUE	TEST
13	3	I	20230730	0		FALSE
14	4	I	20230730	0		FALSE
15	5	I	20230730	0		FALSE
16	6	I	20230730	0		FALSE
17	7	I	20230730	0		FALSE
18	8	I	20230730	0		FALSE
19	9	I	20230730	0		FALSE
20	1	A	20230730	20230730		FALSE
21	2	A	20230730	20230731		TRUE	TEST
22	3	A	20230731	20230730		TRUE	TEST
23	4	A	20230729	20230730		TRUE	TEST
24	5	A	20230730	20230729		TRUE	TEST
25	6	A	20230730	20230730		FALSE
26	7	A	20230730	20230730		FALSE
27	8	A	20230730	20230730		FALSE
28	9	A	20230730	20230730		FALSE
Sheet1

 

[Tetra201]

See if the following formula works for you:

=OR(IF(B2="F",$I$1,$K$1)<>C2,IF(B2="F",$I$1,$K$1)<>IF(D2,D2,C2))

The formula can be compacted further, but in Excel 2016 it would need to be entered using Ctrl+Shift+Enter, not just Enter:

=OR(IF(B2="F",$I$1,$K$1)<>CHOOSE({1,2},C2,IF(D2,D2,C2)))

 

[ItalianPlatinum]

sorry one addition, there is actually another indicator "B", that isnt being captured when applying this. indicators F and B operate the same. I and A operate with the variable 2, while indicator I has variable 1 as well. Apologies should've included see below:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Account	Indicator	Date 1	Date 2	Formula (HELP)	Desired Result		Next BD -2	20230731	LAST BD PM-1	20230730
2	1	F	20230731	20230731	FALSE	FALSE
3	2	F	20230731	20230731	FALSE	FALSE
4	3	F	20230731	20230731	FALSE	FALSE
5	4	F	20230731	20230731	FALSE	FALSE
6	5	F	20230731	20230731	FALSE	FALSE
7	6	F	20230731	20230731	FALSE	FALSE
8	7	F	20230730	20230730	TRUE	TRUE	TEST
9	8	F	20230730	20230731	TRUE	TRUE	TEST
10	9	F	20230731	20230730	TRUE	TRUE	TEST
11	1	I	20230731	0	TRUE	TRUE	TEST
12	2	I	20230729	0	TRUE	TRUE	TEST
13	3	I	20230730	0	FALSE	FALSE
14	4	I	20230730	0	FALSE	FALSE
15	5	I	20230730	0	FALSE	FALSE
16	6	I	20230730	0	FALSE	FALSE
17	7	I	20230730	0	FALSE	FALSE
18	8	I	20230730	0	FALSE	FALSE
19	9	I	20230730	0	FALSE	FALSE
20	1	A	20230730	20230730	FALSE	FALSE
21	2	A	20230730	20230731	TRUE	TRUE	TEST
22	3	A	20230731	20230730	TRUE	TRUE	TEST
23	4	A	20230729	20230730	TRUE	TRUE	TEST
24	5	A	20230730	20230729	TRUE	TRUE	TEST
25	6	A	20230730	20230730	FALSE	FALSE
26	7	A	20230730	20230730	FALSE	FALSE
27	8	A	20230730	20230730	FALSE	FALSE
28	9	A	20230730	20230730	FALSE	FALSE
29	1	B	20230731	20230731	TRUE	FALSE
30	2	B	20230731	20230731	TRUE	FALSE
31	3	B	20230731	20230731	TRUE	FALSE
32	4	B	20230731	20230731	TRUE	FALSE
33	5	B	20230731	20230731	TRUE	FALSE
34	6	B	20230731	20230731	TRUE	FALSE
35	7	B	20230730	20230730	FALSE	TRUE	TEST
36	8	B	20230730	20230731	TRUE	TRUE	TEST
37	9	B	20230731	20230730	TRUE	TRUE	TEST
Sheet1

Cell Formulas
Range	Formula
E2	E2	=OR(IF(B2="F",$I$1,$K$1)<>CHOOSE({1,2},C2,IF(D2,D2,C2)))
E3:E37	E3	=OR(IF(B3="F",$I$1,$K$1)<>CHOOSE({1,2},C3,IF(D3,D3,C3)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tetra201]

See if the following updated formula works for you:

=OR(IF(OR(B2={"A","I"}),$K$1,$I$1)<>CHOOSE({1,2},C2,IF(D2,D2,C2)))

 

[ItalianPlatinum]

Few of my test conditions are failing are you seeing the same? seem like conditions where one or the other isn't the right date not identifying as an issue

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Account	Indicator	Date 1	Date 2	Formula (HELP)	Desired Result		Next BD -2	20230731	LAST BD PM-1	20230730
2	1	F	20230731	20230731	FALSE	FALSE
3	2	F	20230731	20230731	FALSE	FALSE
4	3	F	20230731	20230731	FALSE	FALSE
5	4	F	20230731	20230731	FALSE	FALSE
6	5	F	20230731	20230731	FALSE	FALSE
7	6	F	20230731	20230731	FALSE	FALSE
8	7	F	20230730	20230730	TRUE	TRUE	TEST
9	8	F	20230730	20230731	TRUE	TRUE	TEST
10	9	F	20230731	20230730	FALSE	TRUE	TEST
11	1	I	20230731	0	TRUE	TRUE	TEST
12	2	I	20230729	0	TRUE	TRUE	TEST
13	3	I	20230730	0	FALSE	FALSE
14	4	I	20230730	0	FALSE	FALSE
15	5	I	20230730	0	FALSE	FALSE
16	6	I	20230730	0	FALSE	FALSE
17	7	I	20230730	0	FALSE	FALSE
18	8	I	20230730	0	FALSE	FALSE
19	9	I	20230730	0	FALSE	FALSE
20	1	A	20230730	20230730	FALSE	FALSE
21	2	A	20230730	20230731	FALSE	TRUE	TEST
22	3	A	20230731	20230730	TRUE	TRUE	TEST
23	4	A	20230729	20230730	TRUE	TRUE	TEST
24	5	A	20230730	20230729	FALSE	TRUE	TEST
25	6	A	20230730	20230730	FALSE	FALSE
26	7	A	20230730	20230730	FALSE	FALSE
27	8	A	20230730	20230730	FALSE	FALSE
28	9	A	20230730	20230730	FALSE	FALSE
29	1	B	20230731	20230731	FALSE	FALSE
30	2	B	20230731	20230731	FALSE	FALSE
31	3	B	20230731	20230731	FALSE	FALSE
32	4	B	20230731	20230731	FALSE	FALSE
33	5	B	20230731	20230731	FALSE	FALSE
34	6	B	20230731	20230731	FALSE	FALSE
35	7	B	20230730	20230730	TRUE	TRUE	TEST
36	8	B	20230730	20230731	TRUE	TRUE	TEST
37	9	B	20230731	20230730	FALSE	TRUE	TEST
Sheet1

Cell Formulas
Range	Formula
E2:E37	E2	=OR(IF(OR(B2={"A","I"}),$K$1,$I$1)<>CHOOSE({1,2},C2,IF(D2,D2,C2)))

 

[Tetra201]

Few of my test conditions are failing...

Most likely, you did not enter the formula using Ctrl+Shift+Enter. The array formula from Post #4 has been tested to return the expected results in both Excel 2010 and Excel 2021.

To avoid confusion, try using a regular (i.e., non-array) version of the formula:

=OR(IF(OR(B2={"A","I"}),$K$1,$I$1)<>C2,IF(OR(B2={"A","I"}),$K$1,$I$1)<>IF(D2,D2,C2))

 

[ItalianPlatinum]

oh your right on the array. How do I apply that via VBA then? i was doing this:

.Range("Y11:Y" & Cells(rows.count, 16).End(xlUp).row).Formula =

 

[Tetra201]

Try using the FormulaArray property instead of Formula in your VBA code.

 

[ItalianPlatinum]

I tried that but it keeps the same formula for all the rows?

 

[MARK858]

@ItalianPlatinum try applying the array formula like below

Sub test()

    With Range("E2:E37")
        .Formula = "=OR(IF(OR(B2={""A"",""I""}),$K$1,$I$1)<>CHOOSE({1,2},C2,IF(D2,D2,C2)))"
        .FormulaArray = .FormulaR1C1
    End With
 
End Sub