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

[rosieribbons]

[TABLE="width: 206"]
<tbody>[TR]
[TD](A) day[/TD]
[TD](B) calldate[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"]01/05/2015 14:11[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"]08/05/2015 11:47[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]09/03/2015 14:40[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"]22/05/2015 15:33[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]19/05/2015 16:13[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]10/03/2015 09:24[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]12/05/2015 11:34[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]30/03/2015 11:55[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]21/04/2015 09:49[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD="align: right"]10/04/2015 15:54[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]31/03/2015 14:23[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD="align: right"]01/04/2015 13:12[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD="align: right"]05/03/2015 14:25[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]27/04/2015 09:51[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]07/04/2015 15:36[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD="align: right"]09/03/2015 13:16[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]24/03/2015 09:19[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD="align: right"]09/04/2015 13:38[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]31/03/2015 13:21[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD="align: right"]03/03/2015 09:11[/TD]
[/TR]
</tbody>[/TABLE]

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?

[TABLE="width: 1256"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Time of day[/TD]
[TD]number of calls (total)[/TD]
[TD] number of calls (average day)[/TD]
[TD]average Monday[/TD]
[TD]average Tuesday[/TD]
[TD]average Wednesday[/TD]
[TD]average Thursday[/TD]
[TD]Average Friday[/TD]
[/TR]
[TR]
[TD]0900 - 1000[/TD]
[TD="align: right"]527[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1000 - 1100[/TD]
[TD="align: right"]620[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1100 - 1200[/TD]
[TD="align: right"]528[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1200 - 1300[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1300 - 1400[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1400 - 1500[/TD]
[TD="align: right"]549[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1500 - 1600[/TD]
[TD="align: right"]508[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1600 - 1700[/TD]
[TD="align: right"]377[/TD]
[TD="align: right"]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 717"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
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:

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


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?