Check if a list of dates falls between any one list of dates

Question

I have a spreadsheet like this:

      A        B        C       D
01 11/10/21 25/09/21 10/10/21
02 29/11/21 
03 17/01/22 17/12/21 30/01/22
04 07/03/22
05 25/04/22 09/04/22 25/04/22
06 13/06/22 25/06/22 17/07/22
07 01/08/22
08 19/09/22 24/09/22 09/10/22
09 07/11/22
10 26/12/22 16/12/22 31/01/23
11 13/02/23
12 03/04/23

Basically, the dates in the A column are my data. The dates in B and C represent intervals. So, B1 and C1 mean "from 25/09/21 to 10/10/21".

I can easily have this in D1, to tell me if the date in A1 falls between B1 and C1:

D1 => =AND(A1 > B1, A1 < C1)

But, I need it to tell me if that dates falls in ANY one of those. So, I can write:

D1 => =OR(AND(A1>B1, A1<C1), AND(A1>B2, A1<C2), ..., AND(A3>B12, A1<C12))

OK, it's ugly, but it does the job. I really did think I could get away with this. But... Then I need to repeat the process for ALL of them (A1, B1, C1), comparing EACH one with EACH range on the right. Like this:

D1 -> =OR(AND(A1>B1, A1<C1), AND(A1>B2, A1<C2), ..., AND(A1>B12, A1<C12))
D2 -> =OR(AND(A2>B1, A2<C1), AND(A2>B2, A2<C2), ..., AND(A2>B12, A2<C12))
D3 -> =OR(AND(A3>B1, A3<C1), AND(A3>B2, A3<C2), ..., AND(A3>B12, A3<C12))

And it NEEDS to be written like this (ugh) since smart cut&pasting will mess up the lot. My current solution is totally terrible. I assign this to the first one:

=OR(
AND(A1>$C$1 ,A1<$D$1 ),
AND(A1>$C$2 ,A1<$D$2 ),
AND(A1>$C$3 ,A1<$D$3 ),
AND(A1>$C$4 ,A1<$D$4 ),
AND(A1>$C$5 ,A1<$D$5 ),
AND(A1>$C$6 ,A1<$D$6 ),
AND(A1>$C$7 ,A1<$D$7 ),
AND(A1>$C$8 ,A1<$D$8 ),
AND(A1>$C$9 ,A1<$D$9 ),
AND(A1>$C$10,A1<$D$10),
AND(A1>$C$11,A1<$D$11),
AND(A1>$C$12,A1<$D$12),
AND(A1>$C$13,A1<$D$13),
AND(A1>$C$14,A1<$D$14),
AND(A1>$C$15,A1<$D$15)
)

(I came up with this as I wrote this question) And then paste it again to all of the others. That way, the smart paste will make sure A1 becomes A2 in the second row, and so on. However, it just feels. So. Ugly.

Is there a better way to do this?

Bonus question: how do I make the date in A1 RED if D1 is "TRUE"?

Thanks in advance.

Answer 1

In D2 add formula:

=ArrayFormula(IF(LEN(A2:A),(A2:A>B2:B)*(A2:A<C2:C)>0,))

Bonus: Add conditional formatting rule for range A2:A:

=IF(LEN(A2),(A2>$B$2:B)*(A2<$C$2:C)>0,)

Answer 2

Try this formula in cell D1 and drag down:

=ArrayFormula(IF(SUM((A1>$B$1:$B$12)*(A1<$C$1:$C$12))>0;TRUE;FALSE))

For the question related to conditional formatting, select the range A1:A12 and apply this custom formula as a rule:

=D1=TRUE

Finally, this is the result that we got:

You can find an example here.