Counting frequency of occurence by hour within a date/time cell

[rosieribbons]

(A) day	(B) calldate
Friday	01/05/2015 14:11
Friday	08/05/2015 11:47
Monday	09/03/2015 14:40
Friday	22/05/2015 15:33
Tuesday	19/05/2015 16:13
Tuesday	10/03/2015 09:24
Tuesday	12/05/2015 11:34
Monday	30/03/2015 11:55
Tuesday	21/04/2015 09:49
Friday	10/04/2015 15:54
Tuesday	31/03/2015 14:23
Wednesday	01/04/2015 13:12
Thursday	05/03/2015 14:25
Monday	27/04/2015 09:51
Tuesday	07/04/2015 15:36
Monday	09/03/2015 13:16
Tuesday	24/03/2015 09:19
Thursday	09/04/2015 13:38
Tuesday	31/03/2015 13:21
Tuesday	03/03/2015 09:11

<tbody>
</tbody>

Hi guys,

hope you can help.

Here's a sample of the data I'm working with. They relate to phone calls received (other columns on the sheet too but irrelevant for these purposes.) There are over 4,000 rows in total, spanning a 3 month period (about 50 calls a day over an 8 hour period). I need to count and show:

- The number of calls received on average across a working week, per hour between 9am and 5pm.
(eg between 9am and 10am, we receive 27 calls, between 10am and 11am - 45 calls)

- The same data, but differentiated across the days of the week (eg on specifically Mondays between 9am and 10am we receive an average of 32 calls, but on tuesdays 9am and 10am it's an average of 16 calls)

Can anyone help? I've been using Countif for most of my other work but can't seem to get my head around whether it would work for this. I've seen similar posts suggesting a table array but with so many rows of different dates, I am hoping there's another way?!


Thanks,

 

[123rickfear]

you would need to split out the date and time to separate columns. the easiest way( that I know of) is to have a column with the formula =int(B2) in your example this would return 01/05/2015. In another column you then do =b2-c2 (c2 being your new column), this would then return the time. you could then do a countifs formula like the below to determine how many are between 9am and 10am.

=COUNTIFS(D:D,">"&"09:00",D:D,"<"&"10:00")

 

[rosieribbons]

thankyou! I really appreciate your help.

I think that has worked a treat - although I'm a bit worried that my count for 12-1 and 1-2pm is the exact same figure... this strikes me as a bit too much of a coincidence. Have I not accounted for 24 hour clock?

Also, do you know how I might go on to populate the cells in the right of this table? To get data specifically on days of the week?

Time of day	number of calls (total)	number of calls (average day)	average Monday	average Tuesday	average Wednesday	average Thursday	Average Friday
0900 - 1000	527	8
1000 - 1100	620	10
1100 - 1200	528	8
1200 - 1300	505	8
1300 - 1400	505	8
1400 - 1500	549	9
1500 - 1600	508	8
1600 - 1700	377	6

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The next phase I have to look at is how average call duration fluctuates across the working day (ie when do our most complex enquiries come in?) Sample data:

day	calldate	just date	just time	duration (seconds)	Duration (minutes & seconds)
Friday	01/05/2015 14:11	01/05/2015	14:11:16	6779	52:59
Friday	08/05/2015 11:47	08/05/2015	11:47:37	5440	30:40
Monday	09/03/2015 14:40	09/03/2015	14:40:47	4806	20:06
Friday	22/05/2015 15:33	22/05/2015	15:33:15	4628	17:08
Tuesday	19/05/2015 16:13	19/05/2015	16:13:49	4358	12:38
Tuesday	10/03/2015 09:24	10/03/2015	09:24:14	3927	05:27
Tuesday	12/05/2015 11:34	12/05/2015	11:34:07	3455	57:35
Monday	30/03/2015 11:55	30/03/2015	11:55:18	3365	56:05
Tuesday	21/04/2015 09:49	21/04/2015	09:49:46	3239	53:59
Friday	10/04/2015 15:54	10/04/2015	15:54:26	3188	53:08
Tuesday	31/03/2015 14:23	31/03/2015	14:23:18	3028	50:28
Wednesday	01/04/2015 13:12	01/04/2015	13:12:27	2970	49:30
Thursday	05/03/2015 14:25	05/03/2015	14:25:09	2831	47:11
Monday	27/04/2015 09:51	27/04/2015	09:51:10	2825	47:05
Tuesday	07/04/2015 15:36	07/04/2015	15:36:00	2813	46:53
Monday	09/03/2015 13:16	09/03/2015	13:16:41	2762	46:02
Tuesday	24/03/2015 09:19	24/03/2015	09:19:25	2741	45:41
Thursday	09/04/2015 13:38	09/04/2015	13:38:08	2702	45:02
Tuesday	31/03/2015 13:21	31/03/2015	13:21:11	2679	44:39
Tuesday	03/03/2015 09:11	03/03/2015	09:11:17	2656	44:16

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thankyou so much

 

[123rickfear]

maybe, don't know without seeing the formula!

if you want to find out how many calls were during a certain time period on a certain day you can do a really simple add to your current countifs formula

=COUNTIFS(D:D,">"&"09:00",D:D,"<"&"10:00") your current formula
=COUNTIFS(D:D,">"&"09:00",D:D,"<"&"10:00",A:A,"Monday") would give you all calls between 9am and 10am on a Monday

is this what you're hoping to find?