Google Sheet double TRUE giving a FALSE-CodePudding

I got a weird case of 2 TRUE giving a FALSE.

I got this formula (I reduced it to only have the case that interest me for debugging purpose.:

=ARRAYFORMULA(
IF(ROW(JS7:JS)=7,JR7 1,
    SWITCH($F7:$F,
        "M",1,
        "D",2,
        "W",3,
        "B",IF(AND(JS$7 >= 'A 2021'!$E7:$E,mod(('A 2021'!$E7:$E-JS$7),14)=0),$D7:$D,4),
        "A",5,
        "O",6,
        0)
    )
)

Basically this formula check if the row is the 7th, if yes, add 1 to the previous date to get the next day (this part is fine), else it goes into the Switch to return a value (0 to 6) based on other columns (in my case it is the "B" and should return 4).

This part in the formula return a FALSE:

AND(JS$7 >= 'A 2021'!$E7:$E,mod(('A 2021'!$E7:$E-JS$7),14)=0

yet JS$7 >= 'A 2021'!$E7:$E (checking if we are past the current date) is TRUE and mod(('A 2021'!$E7:$E-JS$7),14)=0 (using modulo to check if we are every 2 weeks) is also TRUE

I tried to replace the returned value of 4 by JS$7 >= 'A 2021'!$E7:$E to see its value and it was TRUE, same thing for the mod(). But when I tried with the AND(), it returned FALSE, yet, that AND() is made of previous parts that both returned TRUE.

Am I doing an obvious mistake here or something is fishy?

CodePudding user response：

AND is not supported under ARRAYFORMULA. try:

=ARRAYFORMULA(
 IF(ROW(JS7:JS)=7,JR7 1,
    SWITCH($F7:$F,
        "M",1,
        "D",2,
        "W",3,
        "B",IF((JS$7 >= 'A 2021'!$E7:$E)*(MOD(('A 2021'!$E7:$E-JS$7),14)=0),$D7:$D,4),
        "A",5,
        "O",6,0)))