+ Reply to Thread
Results 1 to 6 of 6

Scheduling help

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Scheduling help

    Hello. I'm looking for a bit of help with breaking down phone call volume. How would I write the formula to see call volume by 15 min intervals? An example of the data is below.
    Thank you very much!
    -----------------------------------------
    Start Date/Time Duration
    7/1/2013 8:40 0:22:54
    7/1/2013 8:41 0:04:18
    7/1/2013 8:42 0:05:12
    7/1/2013 8:42 0:14:06
    7/1/2013 8:43 0:02:48
    7/1/2013 8:44 0:11:48
    7/1/2013 8:44 0:12:42
    7/1/2013 8:44 0:04:36
    7/1/2013 8:45 0:14:30
    7/1/2013 8:45 0:02:18

  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: Scheduling help

    I would first recommend retitling your post to something others could find, such as compiling data into 15 minute increments.

    Are you trying to get total calls or total duration for the interval?

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

    Re: Scheduling help

    I totaled your data by first adding column A and Column B, and then formatting that result into date/time.

    Then somewhere else just put a start date and time. I went with "7/1/2013 00:15" in E2. In the cell below that put =E2+(1/96) and copy down as far as needed.

    In F2 I put =SUMPRODUCT(--($D$2:$D$11>=E2),--($D$2:$D$11<E3)) and copied it downwards a few hundred rows.

    This sums the calls in the interval in F.

    For sum of duration in column G, just add another layer to the Sumproduct

    =SUMPRODUCT(--($D$2:$D$11>=E2),--($D$2:$D$11<E3),--($C$2:$C$11))

  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: Scheduling help

    Here's the finished example.

    Attachment 260034

  5. #5
    Registered User
    Join Date
    08-22-2013
    Location
    dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Scheduling help

    DAffodil11,
    Thank you so much for the help. I'm sorry I'm a bit slow...When I started working with the whole sheet it was not adding up. Let me try to explain again and hopefully more detailed. I understand breaking down the time intervals in 15 sections.. perfect. What I then want to do is count the number of calls regardless of duration that come in with in that 15 min interval.
    So in the data above I would have 10 calls for the interval if no calls came in before 8:40. I was trying to use an IF formula to say IF between 8 and 8:15 count as 1 but I just cant get it correct.
    Again thank you and I'm sorry for being slow

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

    Re: Scheduling help

    The reason my interval is showing 8 and 2 is the same reason midnight is not considered to have occurred in the previous day.

    Calls that come in at 08:45 did not arrive in the interval from 08:30 to 08:45. They arrive in the next interval. If the calls had arrived at or before 08:44:59 they would have been counted.

    08:30 - 08:45 - 8 calls
    08:45 - 09:00 - 2 calls

+ 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. Scheduling: Calendar style scheduling based on scheduling list
    By denkatprieto in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-19-2012, 11:10 AM
  2. Scheduling
    By Lghtning4u in forum Excel General
    Replies: 1
    Last Post: 03-20-2008, 01:43 PM
  3. help with job scheduling
    By m1cha3lo0o in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-19-2007, 06:31 PM
  4. scheduling
    By burgerboy2k6 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2007, 02:25 PM
  5. scheduling
    By vikkam in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-25-2006, 06:37 PM

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