Excel: logical operators in ARRAYFORMULA

Question

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?

player0 · Accepted Answer · 2021-12-17 02:05:14Z

1

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)))}

edited Dec 17, 2021 at 2:05

answered Dec 17, 2021 at 1:54

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maths noob Over a year ago

ha just figured it out but you beat me to it! :) thanks!

Collectives™ on Stack Overflow

Excel: logical operators in ARRAYFORMULA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related