Forum Discussion
Overlapping times within one row
Hello,
What I would like to do is identify if two time ranges within the same row overlap with each other. My ideal end goal would look like:
Start 1 | End 1 | Client | Start 2 | End 2 | Service | Overlap? |
| 8:55am | 8:59am | NAME | 12:09pm | 12:19pm | SERVICE | FALSE |
| 10:01am | 10:48am | NAME | 10:00am | 11:10am | SERVICE | TRUE |
| 10:17am | 11:17am | NAME | 10:40am | 10:58am | SERVICE | TRUE |
| 10:30am | 11:00am | NAME | 10:30am | 11:14am | SERVICE | TRUE |
| 10:50am | 11:05am | NAME | 2:01pm | 2:38pm | SERVICE | FALSE |
| 10:50am | 11:00am | NAME | 9:00am | 10:05am | SERVICE | FALSE |
I have attempted methods like SUMPRODUCT to identify overlaps, but each one appears to work until I double check and find that it does not identify all overlapping times. For instance, these two rows will be listed as false, even though the times are overlapping.
| 9:59am | 11:04am | NAME | 10:00am | 10:45am | SERVICE | FALSE |
| 9:58am | 10:55am | NAME | 9:45am | 10:25am | SERVICE | FALSE |
Thank you in advance for your help!
you can try this:
= (endTime2 > startTime1) * (startTime2 < endTime1)
and if you want it to be specifically T/F insteas of 1/0 then just
= (endTime2 > startTime1) * (startTime2 < endTime1) = 1
note this does assume all your times are the same day and each end time > start time
1 Reply
you can try this:
= (endTime2 > startTime1) * (startTime2 < endTime1)
and if you want it to be specifically T/F insteas of 1/0 then just
= (endTime2 > startTime1) * (startTime2 < endTime1) = 1
note this does assume all your times are the same day and each end time > start time