Excel: logical operators in ARRAYFORMULA-CodePudding

I am trying to define a function that calculates the tax year a date is in.
I want to do this using array-formula and have it be auto-generated

This is the full solution that I have:

=ARRAYFORMULA(
  IF(
      ROW(A:A)=1,
      "Tax year",
      IF(
        ISBLANK(A:A),
        "",
        IF(
          OR(MONTH(A:A)>4, AND(MONTH(A:A)=4, DAY(A:A)>=6)),
          YEAR(A:A)&"-"& YEAR(A:A) 1,
          YEAR(A:A)-1 &"-"&YEAR(A:A)              
        )
      )
    )
)

But I have noticed it's not behaving the way I'd expect it to. So I tried to break it down to smaller steps. Here is a spreadsheet demo where all the columns except for the ones with cyan headers are generated using array-formulae.

Note that:

the demo has European formatting for dates.
This is for calculating British tax dates, which starts on 6 April hence the logic around that particular date.

Observations:

AND function is misbehaving as demonstrated by column F, ie header M=4&D>=6.
OR function is misbehaving as demonstrated by column H, ie header M>4 or M=4&D>=6.

This behavior is observed on both google sheets and microsoft excel. Is this a syntax issue?

CodePudding user response：

use:

={"Tax year"; ARRAYFORMULA(IF(ISBLANK(A2:A),,
 IF((MONTH(A2:A)>4) ((MONTH(A2:A)=4)*(DAY(A2:A)>=6))),
 YEAR(A2:A)&"-"& YEAR(A2:A) 1,
 YEAR(A2:A)-1 &"-"&YEAR(A2:A)))}