Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Calls taken After hours

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    We are a call center. open from 8a-9PM 7 days a week.

    However, our local stores are open only from 8:30 AM - 5:30 PM.

    In the excel spreadsheet we log the following
    A-Facility Code & Street Name
    B-Date
    C-Time
    D-Name
    E-Type Call Code Notations

    the letters of course represent which column each piece of information is placed in.
    On average we take about 200 calls for this specific location I am attempting to calculate. Now here is the dilema. They are thinking of adding more staff but need to know how many calls we take before/after the stores normal operating hours. So I need a formula that tells me how many calls came in from 17:30 - 08:30 each day of the week. The dates are in the format 05/02/02 and times are all in militay time. To be honest. I am totalllllly clueless where even to begin on a formula that could do this. ANY ideas or formulas would be thouroughly appreciated.

    Snedman

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is NOT unique to locations, but for Monday you could use
    =SUMPRODUCT(((WEEKDAY(B2:B10))=2)*((C2:C10<"8:30"+0)+(C2:C10>"17:30"+0)))

    where 2 = monday and the week is 1=sunday through 7 = sat.

    Be sure to adjust the range to exclude blanks, or it will not function properly.

    good luck

    [ This Message was edited by: IML on 2002-05-03 09:18 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Might also be worth taking a look at pivot table summary of your data. Try the MrExcel main website for some tips.

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well I am doing somethig wrong cause it just isnt working for me. Keeps saying it is a circular reference. Any other ideas???

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you putting in this the range B2:c10?

    That would cause a circular reference, otherwise you should be okay.

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    IML can I email this to you and you can take a look at it. Or is that asking too much.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's fine. That's a home email address I probably won't be able to look at until Sunday. In the interim, we may be able to resolve if you can tell me,
    1) The range of dates ie(b1:b434)
    2) The rance of time (ie c1:c434)
    3) what cell you want the formula to be put in?

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I'd like to take a look at this one.
    Go ahead and send me a full file if its under a meg.
    Please zip it up if you can. 28k connection here.
    Thanks,
    Tom

  9. #9
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    email sent. it is around 25kb it is called Secure1 Thank you very much for this. I have included my email, AIM, & ICQ numbers also. Please do a reply to my email at home snedman@hotmail.com not from my work address. Since I am off this weekend.

    Thank you very much

  10. #10
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Resent

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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