determine if timestamp in one column falls within a range (array) of other timestamps

[jrtaylor]

I've attached a workbook with several columns. Column B is a list of timestamps. If column B's timestamp falls within any of the "blocked" time ranges shown in columns K and L, I want to note that with a formula in column D. (The formula simply says to write a "N" if column B falls within the "blocked" ranges)

For example if cell B136 has a timestamp 2:25 on 1/15/19, and row 40 of columns K and L shows a time range of 2:00 to 4:00 on 1/15/19, this means we want to block row B136. We will do this with a formula in column D (which simply writes a "N" if row 136 is time-blocked).

Any advice?

Thanks so much!

[Bo_Ry]

Please try at D8

=IF(SUMPRODUCT((--SUBSTITUTE(B8,".","-")>=$K$9:$K$155)*(--SUBSTITUTE(B8,".","-")<=$L$9:$L$155)),"N","")

[MrShorty]

I would probably build something based on the approximate match options for Excel's lookup functions. Here's how I did it:

1) The time stamps in column B are text strings that my copy of Excel does not recognize as date/time serial numbers. The first thing I did was convert them to something my copy of Excel will recognize as date/time serial numbers
1a) Select column B -> Find and Replace dialog -> Find "." -> Replace with "/" -> Replace All. Excel automatically makes the text replacement and converts to real date/time serial numbers.
2) An approximate match lookup will find the row where the "start time" in column K is just less than the lookup value. I can use the result of this lookup to return the "end time" from column L. If this return value is greater than the lookup value, then I know that the lookup value is between the start and end time. I might formulate this as =IF(VLOOKUP(B8,$K$9:$L$155,2,TRUE)>B8,"N","not"). To deal with the N/A errors for those lookup values before the first time, I can nest that inside of an IFERROR() or IFNA() function.

Will something like that work for you?

[jrtaylor]

Thanks Bo and MrShorty! Both ideas are good. I've used SUBSTITUTE before when dealing with the timestamp prob, which works. And the IFERROR also helps. Bo's formula seems to work properly though so I'm going to try it, and if I run into more probs I'll get back to you. I very much the help from both of you.