Advanced "between" formula

[Timen]

Hi all,

I'm trying to figure out whether certain timestamps are between a list of two predefined timestamps. I found this topic ( http://chandoo.org/wp/2010/06/24/between-formula-excel/ ) but there is a problem to what I'm trying to do.

I have a list of timestamps. In a separate sheet, I have pairs of values. For example
Start End
17-04-2017 12:00:02 17-04-2017 12:30:45
17-04-2017 13:13:24 17-04-2017 13:23:56

As you can see, the values don't follow each other sequentially. There is a gap between the end of the previous value, and the start of the new value. I'm not interested in the specific range that a timestamp falls into, just whether the timestamp is in between ANY of the values, or whether it is in the "gap" between the two values. That is, if the value under investigation would be "17-04-2017 12:15:00" I expect "TRUE" and if it's "17-04-2017 12:45:00" I expect "FALSE".

The between formula that was explained in the post I mentioned before doesn't allow for non-sequential timestamps, so that's why I can't use this formula. I've already tried this with IF and AND, that works, but the problem is that I have around 1600 time intervals, and since 1 formula only allows for a nest-size of 64 it would become very complicated.

I'm looking forward to hearing from one of you . Thanks in advance!

 

[bosco_yip]

Hi,

Give 10 rows of example, expected results and upload a file together.

Regards

 

[Timen]

See the attachment. Column D and E would be data on a different sheet, but that doesn't matter for the problem. The number of intervals is around 1600, the number of values to be checked is much higher.

 

[vletm]

Timen
Something like this?

 

[bosco_yip]

2 formula ways.

In G2, array formula (confirm entered with SHIFT+CTRL+ENTER) copy down :

=OR((A2>=$D$2:$D$4)*(A2<=$E$2:$E$4))

OR......

In H2, normal formula (non-CSE) copy down :

=IF(SUMPRODUCT((A2>=$D$2:$D$4)*(A2<=$E$2:$E$4)),TRUE, FALSE)

Regards
Bosco

 

[Timen]

Thank you both very much! That's really helpful .