Countif of Hours and Mins not dates

Afternoon all

I have a data feed that gives a column of cells formated mm/dd/yy hh:mm
These are call entries, what i want to do is count all the occurances each hour, so count all the entries say between 9am and 10am. However the column is very long and holds data for many weeks so i want to be able to ignore the date part of the cell.

For example

3/17/09 9:58
3/18/09 10:58
3/18/09 8:58
3/19/09 8:58
3/19/09 9:58
3/19/09 9:58

So the entries between 9 and 10 are = 3

Ideally i do not want to do text to colums and split out the parts. i would like to leave the source data alone.

Nick

Nick, really you should use a PT for this... you can set your DateTime Column to be ROW Field and Group by Hour only... you can then have DateTime Column in Data Field also set to COUNT. This is far and away the best method available to you... ie will give you an hourly breakdown.

EDIT: If you are interested in only one time span then a SUMPRODUCT should suffice: =SUMPRODUCT(--(HOUR(MOD(A2:A7,1))=C2)) where C2 = Start hour of interest (9) ... to reiterate though for multiple results use a PT (purpose built)

See my EDIT.

Adapt the earlier SUMPRODUCT

D2: 9
E2: =SUMPRODUCT(--(HOUR(MOD($A$2:$A$7,1))=$D2),--($C$2:$C$7="Yes"))
copy down as required where D3 etc hold hours of interest.

Hi Nick,

One other way....
Assuming your dates and times are in column A, if you use a helper column B adjacent to your date/times and enter

=A1-INT(A1)

and copied down,

Then create a table of hours from 1 to 24, in say D2:D25 and in E2 enter

=COUNTIF($B$1:$B$1000,"<"&TEXT(D2/24,"0.000"))-SUM(E$1:E1)

HTH