Overlapping Time Formula

1. Overlapping Time Formula

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

2. Re: Overlapping Time Formula

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?

3. Re: Overlapping Time Formula

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

4. Re: Overlapping Time Formula

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

5. Re: Overlapping Time Formula

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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1