2

I found a thread already with the same question:

How to calculate the overlap between some Google Sheet time frames?

But I tried the same formula, altering it to match the ranges in my google sheet but it is not working.

Here is my google sheet:

https://docs.google.com/spreadsheets/d/19tk3H7G_FnJilXoPzU5on7m6yS33eWy2PhO2WonVUlg/edit?usp=sharing

This is the commenter view.

I gave it a range that should not have overlaps and I think it is this part of the formula which is giving me "True" for everything.

IF(C$29:C$42<D$29:D$42, True,

My goal is to search the range of times and see if any of them overlap but I am not sure how to accomplish it.

Edit: I noticed my array numbers were different 29 vs 42 so I have amended it. I added more times so there are now overlapping times and changed the range numbers

player0
  • 124,011
  • 12
  • 67
  • 124
Puggy
  • 107
  • 1
  • 6

1 Answers1

2
=ARRAYFORMULA(IF(I29:I56="",,
 IF(     I29:I56 <     J28:J55,  TRUE, 
 IF({""; I29:I56}<{""; J28:J55}, TRUE, ))))

the formula will work only if times are sorted:

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thanks, also I just noticed you need to stagger the columns with the right one higher. If it is not to complicated to explain the formula, do you think you could help explain how it works? – Puggy Nov 16 '21 at 22:25
  • firtst IF checks for the "next" overlap while 2nd IF checks for "previous" overlap. yes, columns needs to be offset by 1 row – player0 Nov 16 '21 at 22:37
  • Note that this only checks for local overlaps – Tom Sharpe Jan 17 '23 at 22:19