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

 

[Legacy 98055]

Hi IML

The range of dates is B9 on down
related times are C9 on down
I think he is wanting the totals of any calls > 17:30 < 08:30 for any given day of the week...
Sunday totals
Monday totals
Tuesday ect...

I guess your formula edited for each day would be perfect?

=SUMPRODUCT(((WEEKDAY(B2:B10))=2)*((C2:C10<"8:30"+0)+(C2:C10>"17:30"+0)))

I haven't tried it yet, but am going to try to incorporate it into hiw worksheet unless you have any better solutions?

Thanks,
Tom

 

[IML]

there may be better ways, but I'll stick with the orignal - noting the orignal caveat to be careful about only including non-blanks in the range.

weekday on a blank evaluates to saturday (7), and a blank 0, will evaluate true as to being less than 8:30.
This message was edited by IML on 2002-05-03 15:21