+ Reply to Thread
Results 1 to 4 of 4

Finding overlapping mm:ss within a date

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Cincy, OH USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    2

    Post Finding overlapping mm:ss within a date

    This one was hard to explain in the Title field.... I have a list of dates/times where faxes were sent and received. I'm trying to find a concurrency, or the max number of fax lines I need for our current volume. I've broken down the time from seconds to mm:ss. Obviously if a fax lasts 10 minutes, there are other faxes being sent/received in that time period. According to my data, I'm trying to find the highest concurrency. Below you can see an example. The first fax lasts 27 minutes, while other fax are coming in or being sent. Just by looking here, I see that I need at least 7 concurrent lines. But I have thousands of rows, so I'm trying to find a formula. Make sense?

    i.e.

    Start Time Connection_mm:ss

    8:02:22 PM 27:24
    8:11:40 PM 00:37
    8:14:50 PM 00:22
    8:17:06 PM 00:25
    8:17:06 PM 00:25
    8:17:02 PM 00:00
    8:12:28 PM 07:41
    8:43:42 PM 01:30

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Finding overlapping mm:ss within a date

    I used this calculation for trunk lines at my old office. I arranged the data as thus:

    A2:A9 start time
    B2:B9 connection
    C2: =SUMPRODUCT(($A$2:$A$9>=A2)*($A$2:$A$9+$B$2:$B$9<=A2+B2))

    and copied down

    Sort C Largest to smallest to find max concurrency
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    11-10-2014
    Location
    Cincy, OH USA
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    2

    Re: Finding overlapping mm:ss within a date

    This is great. I still have a question on your "run time". I only have the number of minutes it ran, not a time. Would this still apply? As shown in the original post, I have a date/time for what you call "start time", but my 2nd column is length of time the fax call lasted in mm:ss.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Finding overlapping mm:ss within a date

    That's fine.

    Formula logic:

    =SUMPRODUCT(($A$2:$A$9>=A2)*($A$2:$A$9+$B$2:$B$9<=A2+B2))

    COUNT NUMBER OF CELLS WHERE BOTH ARE TRUE:
    Event begins after or at same time as event in A2
    Event ends before or at same time as connection in B2

    Excel saves all time and date stuff in analagous formats, such that .5 = 12 hours (12/24) and today's date is really the count of days since 1/1/1900.
    Adding times together is just as easy - 11/10/2014 + 01:00:00 = 11/10/2014 1:00 AM. It's just smart like that.

    So let's walk through the formula:
    First expression - Which cells in A2:A9 are >= A2 (8:02:22 PM). All of them, evaluated to {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
    Second expression - A2:A9 + B2:B9 = {0.85400462962963;0.847326388888889;0.845497685185185;0.845497685185185;0.841863425925926;0.843888888888889;0.845162037037037;0.864722222222222}

    Which of these are <= A2 + A2 (0.85400462962963)? All but the 8:43:42 PM entry which begins after the connection for A2 begins. This evaluates to {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE}

    TRUE*TRUE, TRUE*TRUE, TRUE*TRUE, TRUE*TRUE, TRUE*TRUE, TRUE*TRUE, TRUE*TRUE, TRUE*FALSE
    becomes
    1,1,1,1,1,1,1,0

    And then it adds them up

    7!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Finding Overlapping Date Entries with matching IDs
    By Zabaran in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-07-2013, 06:30 AM
  2. Macro for finding Overlapping duration of all the machines in service at a time
    By welsel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2012, 06:12 AM
  3. [SOLVED] Finding Overlapping Data
    By comparini3000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2006, 03:45 PM
  4. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. Overlapping or Duplicate Date & Time
    By Mike in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Tags for this Thread

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