array formula, sorting time and day of the week.

**xatomicx** · 04-12-2012, 12:11 PM

{=SUM(IF((MOD(hdcore1!$F$4:$F$2500,1)>=A2)*(MOD(hdcore1!$F$4:$F$2500,1)<A3),1))}

A2: 08:00
A3: 08:30

This formula helps me calcutate the total # of interaction sets on a sheet called "hdcore1", range f4-f2500, and +1 all interaction between time 08:00 and 08:30.

This formula works fine, but i've got asked to add a little extra.

On sheet hdcore1, data in the F column looks like

04/11 / 08:26
04/11 / 08:26
04/11 / 08:40
04/11 / 08:43
04/11 / 08:53
04/11 / 08:53
04/11 / 08:56
04/11 / 09:09
04/11 / 09:53
04/11 / 09:54
04/11 / 09:54
04/11 / 09:54

Those numbers referred to a formatted =now() value, formatted mm/dd / hh:mm

What i need is:

1rst, is once again, compiling the time, but want the average of calls (not the sum) that were done during the week, sort by time (average of all 08:00 to 08:30 monday to friday), and another row the average of calls that were done during weekend, sort by time (average of all 08:00 to 08:30 saturday and sunday)

I know I can use “weekday” formula to set wich of the days I want, ranging from 1 to 7, I’ve been using this formula before, but array formula is really Chinese to me.

One formula will be using day #1 #7, and second one #2 to #6

**DBY** · 04-12-2012, 03:53 PM

Hello
I've been looking at your post and come up with the attached file. There were a couple of issues though. I found I had to enter a date in with the times in cells A2 and A3 as there seemed to be a discrepency between the MOD calculations in the Call Log Column if I didn't do so. Also the regional setting on my PC sees dates as dd/mm/yy whichever way I format them to appear, so be aware of this on your PC over the pond. I've used the SUMPRODUCT function rather than arrray formula as I believe they're a little more efficient. I hope it's something like what you're looking for and I'd be interested to hear you thoughts.

Regards
DBY