Basic IF & IF/AND Not Working…

[zero269]

Hello,

I seem to be having some annoying issues with trying to get what I thought was a basic IF/AND formula to work. I even tried just a basic IF statement, and that too, is giving me a hard time.

Basically, I'm checking if a value is both >=1 AND <=4 AND if another cell is a number (date).

Here's my Sample Data trying a few scenarios.

I'm evaluating the data in the Left Table, and the Right Table is running a few basic tests.

The RIGHT column is grabbing the right value only from the Period column.

=RIGHT(t_Period[@Period],1)

The 1 Criteria column is supposed to only check if RIGHT is >=1… appears to be working, but it's actually a NO GO when I change the >=1 to >=2 and it returns TRUE for a 1 value.

=LET(
Quarter, RIGHT(t_Period[@Period],1),
IF(Quarter >=1,TRUE,FALSE))

2 Criteria is trying the IF/AND; another NO GO.

=LET(
Quarter, RIGHT(t_Period[@Period],1),
IF(AND(Quarter >=1,Quarter<=4),TRUE,FALSE))

3 Criteria is where I'm checking if the Start Date is a number (ISNUMBER).

=LET(
StartDate, ISNUMBER(t_Period[@Start]),
Quarter, RIGHT(t_Period[@Period],1),
IF(AND(Quarter >=1,Quarter<=4,StartDate),TRUE,FALSE))

Test # is just testing if RIGHT is >2, so why it returns TRUE for that 1 value is beyond me.

=IF([@RIGHT]>=2,TRUE,FALSE)

Any help with the 3 Criteria column would be greatly appreciated...

VBA Testing.xlsm
	A	B	C	D	E	F	G	H	I
1	Period	Start		RIGHT	1 Criteria	2 Criteria	3 Criteria	Expected	Test #
2	1.1	2024-08-16		1	TRUE	FALSE	FALSE	TRUE	TRUE
3	1.2	2024-10-25		2	TRUE	FALSE	FALSE	TRUE	TRUE
4	1.3	2025-01-17		3	TRUE	FALSE	FALSE	TRUE	TRUE
5	1.4			4	TRUE	FALSE	FALSE	TRUE	TRUE
6	1.5	2024-08-16		5	TRUE	FALSE	FALSE	FALSE	TRUE
7	1.6	2025-06-07		6	TRUE	FALSE	FALSE	FALSE	TRUE
IF_AND

Cell Formulas
Range	Formula
D2:D7	D2	=RIGHT(t_Period[@Period],1)
E2:E7	E2	=LET( Quarter, RIGHT(t_Period[@Period],1), IF(Quarter >=1,TRUE,FALSE))
F2:F7	F2	=LET( Quarter, RIGHT(t_Period[@Period],1), IF(AND(Quarter >=1,Quarter<=4),TRUE,FALSE))
G2:G7	G2	=LET( StartDate, ISNUMBER(t_Period[@Start]), Quarter, RIGHT(t_Period[@Period],1), IF(AND(Quarter >=1,Quarter<=4,StartDate),TRUE,FALSE))
I2:I7	I2	=IF([@RIGHT]>=2,TRUE,FALSE)

 

[hagia_sofia]

RIGHT extracts the string as text not as number; adjust your formulas so that they are numbers e.g. like this:

--(RIGHT(t_Period[@Period],1))

 

[zero269]

RIGHT extracts the string as text not as number; adjust your formulas so that they are numbers e.g. like this:

--(RIGHT(t_Period[@Period],1))

Thanks, Hagia,

I made your suggested changes and low and behold, it's working now....

=LET(
StartDate, ISNUMBER(t_Period[@Start]),
Quarter, --(RIGHT(t_Period[@Period],1)),
IF(AND(Quarter >=1,Quarter<=4,StartDate),TRUE,FALSE))

Interestingly enough, I continued testing and tried enclosing my numbers with doublequotes ("1"), and they worked as well. That didn't make any sense to me until I read your statement. Now it makes perfect sense. Let's hope I can remember this in the future...

First I just tried AND and got all FALSE's. That's when I changed 1 to "1" and 4 to "4" and they returned the correct results. I think this is my first time hard coding numbers into the formula... maybe.

I prefer your solution over the double quotes... and I read somewhere that the -- replaces VALUE().

Thanks again... this was very helpful!

VBA Testing.xlsm
	A	B	C				G	H					M	N
1	Period	Start					3 Criteria	Expected					AND	3 Again
2	1.1	2024-08-16					TRUE	TRUE					TRUE	TRUE
3	1.2	2024-10-25					TRUE	TRUE					TRUE	TRUE
4	1.3	2025-01-17					TRUE	TRUE					TRUE	TRUE
5	1.4						FALSE	TRUE					TRUE	FALSE
6	1.5	2024-08-16					FALSE	FALSE					FALSE	FALSE
7	1.6	2025-06-07					FALSE	FALSE					FALSE	FALSE
IF_AND

Cell Formulas
Range	Formula
G2:G7	G2	=LET( StartDate, ISNUMBER(t_Period[@Start]), Quarter, --(RIGHT(t_Period[@Period],1)), IF(AND(Quarter >=1,Quarter<=4,StartDate),TRUE,FALSE))
M2:M7	M2	=AND([@RIGHT]>="1",[@RIGHT]<="4")
N2:N7	N2	=LET( StartDate, ISNUMBER(t_Period[@Start]), Quarter, RIGHT(t_Period[@Period],1), IF(AND(Quarter >="1",Quarter<="4",StartDate),TRUE,FALSE))