Search code examples
google-sheets

How to check if a date time is between multiple time spans?

I have a cell that contains multiple time spans in the following format:

2024-03-20 15:00:00&2024-03-20 16:00:00|2024-03-20 20:15:00&2024-03-21 15:00:00|...

I want to check if a particular date time e.g. 2024-03-20 16:30:00 falls in any of those ranges.

I tried the following formula, but it seems to check the date only against the first range and ignores others:

=ARRAYFORMULA(OR(ISBETWEEN(A1, INDEX(SPLIT(SPLIT(B1, "|"), "&"), 1), INDEX(SPLIT(SPLIT(B1, "|"), "&"), 2))))

Please help to correct the formula.

Solution

  • Here's another approach using LET and SUMPRODUCT

    =LET(
  times,
    ARRAYFORMULA(
      SPLIT(
        TOCOL(
          SPLIT(B1, "|")),
        "&")),
   SUMPRODUCT(
     INDEX(times,,1)<=A1,
     INDEX(times,,2)>=A1)>0)

    =LET(times,ARRAYFORMULA(SPLIT(TOCOL(SPLIT(B1, "|")), "&")), SUMPRODUCT(INDEX(times,,1)<=A1, INDEX(times,,2)>=A1)>0)