Overlap between two time ranges

**LondonJames** · 05-28-2012, 02:04 PM

Hello,

I'm trying to find an elegant (less caveman) way of calculating the overlap between two time ranges.

For example: No overlap, partial overlap, full overlap (enclosed) - ie returning just the time.

So if range 1 = 10:00 to 12:00 and range 2 - 14:00 to 18:00 there will be zero minutes overlap

but if range 1 = 10:00 to 15:23 and range 2 - 14:00 to 18:00 there will be 1:23 overlap

It can be calculated with columns of Ifs, but I want a tidy solution in just one column labelled "overlap"

Can this be done (possibly using sumproduct and some unary operations)?

Many thanks,
James

**daddylonglegs** · 05-28-2012, 02:13 PM

Assuming the times are always on the same day try this formula in row 7 copied down

=MAX(0,MIN(B7,E7)-MAX(A7,D7))

**LondonJames** · 05-28-2012, 03:01 PM

Hi there. Very nice, thanks for the reply so quickly.

Almost there - just one small hitch. It seems to be off, in a few instances by one minute. I didn't specify earlier the accuracy needed. It'll be to the minute.
I'm also calculating the amount of no-overlap by subtracting the range length, and it's this figure which is out by a minute. I'm obviously not counting one, somewhere.

For example:

16:34-19:12 and 05:00-21:05. Your formula returns 2:38 = correct. This minus the fist range length = 0 = correct

19:36-22:08 and 05:00-21:05. Your formula returns 1:29 = correct, but, this minus the first length = 1:02 (2:32-1:29=1:02) = out by 1 minute. Should be 1:03

04:50-06:55 and 04:53-21:13. Your formula returns 2:01 = out by 1 minute. Should be 2:02 overlap, and no-overlap should be 0:03.

There's always one larger range and seeing how the smaller one compares, if that helps?

I'm not sure If I'm doing making a very simple mistake and not seeing it, or if the formula needs tweaking?

Many thanks again.

**daddylonglegs** · 05-28-2012, 03:15 PM

I think the formula is accurate but I suspect your time values probably include seconds that aren't displayed. The formula will actually use the underlying value that includes those seconds, not the displayed values, so that can make the calculations seem incorrect.

When I put those time values in myself (without seconds) all the formulas work OK......but if you want to ignore any seconds you can use some rounding in the formula, e.g. use this formula in J7

=MAX(0,MIN(MROUND(B7,"0:01"),MROUND(E7,"0:01"))-MAX(MROUND(A7,"0:01"),MROUND(D7,"0:01")))

and then this one in K7 for the "no Overlap"

=MROUND(B7,"0:01")-MROUND(A7,"0:01")-J7

In my version these give the same results as the original formula, see attached

**ben_hensel** · 05-28-2012, 03:24 PM

Originally Posted by OP

So if range 1 = 10:00 to 12:00 and range 2 - 14:00 to 18:00 there will be zero minutes overlap

but if range 1 = 10:00 to 15:23 and range 2 - 14:00 to 18:00 there will be 1:23 overlap

I think this is really simple?

A1 = range 1 start
A2 = range 1 end
A3 = range 2 start
A4 = range 2 end
A5 = IF(A2 > A3, A2 - A3, "no overlap")

Of course, my thinking could be wrong if I'm not understanding the question fully.

EDIT:
Okay yeah I didn't understand the question fully.

**LondonJames** · 05-28-2012, 03:51 PM

DaddyLongLegs, you were right. It was a rounding error. My columns of Sunset/Sunrise are calculations based on some interesting Trig. So those exact values were hidden by formatting (hh:mm). I used the MROUND on that column, and it's all working now.

Thanks very much for your help. Greatly appreciate it. This will make my life a lot easier now.

All the best

**papotter** · 09-26-2012, 10:05 PM

This is fantastic, I spent over an hour looking for this formula! Great logic, thanks!

**troot** · 08-20-2014, 12:31 PM

How would you extend this formula to include up to four different In and Out times within one day? I am trying to figure out total overlapping time among four sets of times within one particular day. Any help is greatly appreciated. Thank you!

**aragno** · 11-24-2015, 02:20 AM

Should I create a new question or can I bring this one back to life if my question is similar and related?

**FDibbins** · 11-24-2015, 02:23 AM

Please create a new thread for your question, if you feel this thread is particularly relevant to your need, provide a link to the other thread in your new thread.

**kukku** · 11-23-2018, 08:21 AM

I need to calculate down time between 2 dates, which have multiple time periods as detailed below,

1. 01-01-2018 20:00 (open)01-01-2018 22:00 (close)
2. 01-01-2018 21:00 (open)01-01-2018 21:30 (close)
3. 01-01-2018 21:30 (open)01-01-2018 23:00 (close)

the above described is the data, but I want to exclude the overlap time between these 3 timings and calculate the actual downtime.

How this can be achieved.

**mjeep01** · 07-12-2019, 02:18 AM

Very useful!

Overlap between two time ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Re: Overlap between two time ranges

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions