Number of instances between a time range

**brainache** · 03-01-2007, 04:49 PM

Hi,

We have a xls sheet that records when phone calls have been made. We have a column that works out the day from the date inputed (TEXT(WEEKDAY)function) and the time of the actual call. We need to calculate the number of phone calls on a specified day AND between a particular time (eg.No. of calls on Saturday between 09:00 - 13:00). We would like numerous ranges E.g. Saturday 09:00-12:59, Saturday 13:00-16:59, Sunday 08:00-11:59, Monday ..etc.).

Our workbook looks similar to this:

Date Day of Week Time of Call

01/07/06 Saturday 12:07

and so on

So far we have been through all the functions and formula we can think of, but have hit a brick wall!

Can someone please help us? Would be very much appreciated!

**dominicb** · 03-01-2007, 05:02 PM

Good evening brainache

Try using a formula like this :

=SUMPRODUCT(--(B5:B100="Saturday"),--(C5:C100>=TIMEVALUE("12:00")),--(C5:C100<=TIMEVALUE("19:00")))

This will count all the occurances of :
column B=Saturday
column C is after midday but before 7.00pm

HTH

DominicB

**daddylonglegs** · 03-01-2007, 05:07 PM

Here's one way,

Assuming your dates are in A2:A100, days in B2:B100 and time in C2:C100.

Then in E2:G2 list your criteria E2 = Saturday, F2 = 09:00, G2 = 13:00

In H2 use the formula

=SUMPRODUCT(--(B$2:B$100=E2),--(C$2:C$100>=F2),--(C$2:C$100<G2))

you can then add additional days/times in subsequent rows and simply copy the formula down for each

**brainache** · 03-02-2007, 04:43 AM

Thanks a lot for the quick replies!

So do I basically need fields with my criteria E.g.

09:00-13:00
14:00-16:00 then in the formulae reference/point to these cells for it to compare/meet criteria??

Also, will cell format make a difference?

I just need the output to be a number, but each of the mentioned fields (date, Day and time are all different formats).

Sorry for being a bit of a newbie!

Will give it a go and let you know!

**brainache** · 03-02-2007, 05:01 AM

Thanks for the reply!!

Right basically it looks like this

------A-----------B------------C------------------D----------
1 | Name_______Date_____Day of Week____Time of Call

2 | Joe Bloggs__10/08/06____Saturday*_______11:23
3 | ....
4 | .... and so on

* this isnt manual plain text - the following formula is used (in C2 downwards) to work out day of week (does it matter?): =TEXT(WEEKDAY (B2),"dddd") when user types in date.

Do I need to type in some other cells the time ranges? Cant get my head around how it would work!

Basically I will have some total boxes at the bottom displaying times ranges for Saturday and Sunday and want to show how many calls falls between a certain range.

Hope this helps to explain my brainache! :-)