How to check if any of the time ranges overlap with each other in Google Sheets
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
=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:
