Calls taken After hours

[snedman]

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

[IML]

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 ]

[Mudface]

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

[snedman]

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

[IML]

Are you putting in this the range B2:c10?

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

[snedman]

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

[IML]

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?

[Tom Schreiner]

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

[snedman]

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

[snedman]

Resent