+ Reply to Thread
Results 1 to 2 of 2

Identify and count overlapping date/time entries on a call log

  1. #1
    Registered User
    Join Date
    09-23-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    1

    Identify and count overlapping date/time entries on a call log

    Hi there,

    I am hoping that someone can help me. I have a task that I would like to accomplish and that is to understand the maximum number of simultaneous calls taking place on our phone system on a daily basis.

    I had hoped that our phone system would have some sort of reporting built in to it, but sadly not, it merely outputs a load of data - an entry for every call that took place on the system.

    I've included some examples in the attached spreadsheet.

    Would some kind person please show me how to write a macro that looks at the entries on the sheet, identifies which calls overlapped, and then produce a total for each day? Just to clarify, if on a given day, there were 2 calls that overlapped in the morning, and then 3 calls that overlapped in the afternoon then I would only be interested in the 3 overlapping calls as I'm trying to understand the maximum number of overlapped/simultaneous calls during a day (i hope that makes sense).

    For example, in the spreadsheet I have call logs from 24th June and 25th June, and I would love a macro to generate a report stating:

    24/06/2012: 0
    25/06/2012: 4

    Any help would be hugely appreciated.

    Many thanks in advance for even reading this,

    AndrewCall_log.xls

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Identify and count overlapping date/time entries on a call log

    Hi

    Try this macro.

    Please Login or Register  to view this content.
    Columns G & H need to be clear and G formated as a date.

    If works by taking every second of the day and determining how many calls it falls between. It's a bit of a sledgehammer but seems to work OK.


    ~~~~~~~~~
    Open up the VBA editor by hitting ALT F11

    Insert a new module by hitting Insert - Module

    Paste the macro into the empty sheet

    Hit ALT F11 to get back to the worksheet.

    Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.
    Martin

+ 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