Calls taken After hours

snedman

New Member
Joined
May 2, 2002
Messages
39
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
Might also be worth taking a look at pivot table summary of your data. Try the MrExcel main website for some tips.
 
Upvote 0
Well I am doing somethig wrong cause it just isnt working for me. Keeps saying it is a circular reference. Any other ideas???
 
Upvote 0
Are you putting in this the range B2:c10?

That would cause a circular reference, otherwise you should be okay.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top