Overlapping Time Formula

**misterzr** · 01-19-2011, 06:36 PM

I need a formula to identify overlapping times in Excel

Here is my data

Date | Start Time | End Time
11/01/2010 8:58:00 AM 9:20:00 AM
11/01/2010 9:25:00 AM 10:02:00 AM
11/01/2010 10:58:00 AM 11:09:00 AM
11/01/2010 11:00:00 AM 11:23:00 AM
11/01/2010 11:25:00 AM 11:55:00 AM
11/01/2010 12:26:00 PM 1:30:00 PM
11/01/2010 12:56:00 PM 1:57:00 PM
11/01/2010 12:56:00 PM 4:46:00 PM
11/01/2010 12:59:00 PM 2:12:00 PM
11/01/2010 1:58:00 PM 3:08:00 PM
11/01/2010 3:30:00 PM 4:02:00 PM
11/02/2010 7:46:00 AM 12:33:00 PM
11/02/2010 8:51:00 AM 9:25:00 AM
11/02/2010 9:00:00 AM 9:25:00 AM
11/02/2010 9:52:00 AM 10:59:00 AM
11/02/2010 9:55:00 AM 11:15:00 AM
11/02/2010 9:58:00 AM 11:40:00 AM
11/02/2010 9:59:00 AM 11:05:00 AM
11/02/2010 11:00:00 AM 11:40:00 AM
11/02/2010 12:47:00 PM 2:42:00 PM
11/02/2010 12:50:00 PM 1:24:00 PM
11/02/2010 12:55:00 PM 2:11:00 PM
11/02/2010 1:01:00 PM 2:12:00 PM
11/02/2010 1:03:00 PM 2:12:00 PM
11/02/2010 1:56:00 PM 2:27:00 PM
11/02/2010 1:57:00 PM 2:29:00 PM

**daddylonglegs** · 01-19-2011, 06:47 PM

So what sort of result do you need for that data?

Do you just want to mark the entries that overlap.....or list the actual overlapping periods....or calculate the total overlap....or something else?

**misterzr** · 01-20-2011, 10:23 AM

I just basically need to identify how many overlaps there are at any given time. Basically trying to count concurrent sessions.

**misterzr** · 01-21-2011, 05:40 PM

Figured this out on my own, here is my solution

=SUMPRODUCT(($H$2:$H$1001=$H2)*($I$2:$I$1001<I2)*($L$2:$L$1001>I2))+1

**Junior4** · 01-21-2011, 05:55 PM

I'm sorry this isnt my post but i have a silmilar need, so i dont want to start a new thread i have attached a file. what i want is for the intervals that overlaps to be highlighted.