+ Reply to Thread
Results 1 to 5 of 5

Overlapping Time Formula

  1. #1
    Registered User
    Join Date
    06-22-2010
    Location
    Toon Town
    MS-Off Ver
    Excel 2003
    Posts
    12

    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. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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?
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-22-2010
    Location
    Toon Town
    MS-Off Ver
    Excel 2003
    Posts
    12

    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. #4
    Registered User
    Join Date
    06-22-2010
    Location
    Toon Town
    MS-Off Ver
    Excel 2003
    Posts
    12

    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. #5
    Registered User
    Join Date
    11-16-2010
    Location
    Grenada
    MS-Off Ver
    Excel 2010
    Posts
    14

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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