Counting events occurring between specific time windows and on certain days

[Gyrene4341]

Hello. I'm in over my head again. So, gurus, I again beg for help.

I have a list of date/time data entries. From those, I'm trying to create a function/formula that counts which occured on a Monday and between which 30-min increment. So how many logins were on Monday from 8-8:30 am and so on. Workbook is attached.

A
4/1/16 10:01
4/1/16 10:02

B (Mondays)
08:00
08:30

[PaulM100]

Something like this:

B1 =TEXT(DAY(A1),"dddd")
E2 ==SUMPRODUCT(($B$1:$B$2827=$D$2)*(MOD($A$1:$A$2827, 1)>=$D3)*(MOD($A$1:$A$2827, 1)<=$D4))

[Gyrene4341]

That works! (I was secretly hoping to not have to modify anything like adding that extra column so my replacement who was born tragically without a brain could just add the new entries and the function would keep up) Thank you!

[johnmpl]

Hi, to all!

[D3] : =SUMPRODUCT(--(WEEKDAY(A$1:A$2827)=2),--(MOD(A$1:A$2827,1)>=C3),--(MOD(A$1:A$2827,1)<C3+"0:30"))
And drag it down.

P.D. The interval must be "closed" at first and "open" at the end (the last hour of interval is including in next range).
P.D. Matrix form of SUMPRODUCT is faster than multiplication of ranges into SUMPRODUCT.

Check file. Blessings!