Excel - Find a date within a list of arrays

Question

I need to enter the text 'AL' into cell E3 if the date in E2 is a date someone took as annual leave.

Cells A3:B11 list a start date and an end date someone took as annual leave. See below.

I need a way of checking if a date is between (and including) an array of those two dates, for each from in cells A3:B11.

Note: Cell A5 is one day someone took as annual leave and just requires a start date only.

EDIT: I can use the following algorithm for the first row (A3:B3)

=IF(SUMPRODUCT((ROW(INDIRECT(A3&":"&B3))=E3)*1)=1,"AL","")

I am still stuck however on applying for all possible rows

Answer 1

I believe I've found a solution with the help of Hoejanson.

=IF(SUMPRODUCT(((A3:A11<=E3)*1),((B3:B11>=E3)*1))=1,"AL","")

It will mean ensuring that both start and end dates are filled for when there is just one day so cell B5 would have to equal 07/07/2018 for this to work.

EDIT: Found a way to allow just a date in the start column to be added.

=IF(SUMPRODUCT((A3:A11<=E3*B3:B11>=E3)+(A3:A11=E3*B3:B11=""))=1,"AL","")

What this is saying is if column A's dates are less than the value in E3, AND column C's date are larger than evaluate true or false. Then the + means 'OR', where column A's dates are equal to E3 AND the corresponding rows in column C are empty. Hope this helps anyone searching a similar answer.

Answer 2

Using your example sheet, in cell E3 use the formula

=IF(AND($E$2>=A3,$E$2<=B3),"AL","-")

You can then apply this down as many rows as you need. If you need to drag it to the right, across columns, then be sure to switch the cell references (i.e. to hit different dates in cell F2, G2, H2, etc you would use (=IF(AND($F$2>=A3,$F$2<=B3),"AL","-")

The only issue with this method is that you'll need to fill in the End Date for leaves only lasting a day. So just copy that 7/7/2017 over into column B.

P.S. if you actually meant "Array" in the official excel array formula sense, then the solution is different.