Countif of Hours and Mins not dates

[Nick_in_Dubai]

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

[DonkeyOte]

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)

[Nick_in_Dubai]

yeah a PT would be best but not available to me in this instance, their are many other colums for different info types all linking into an easy presentatiion for the end user.

The colum is not huge a few hundread entries, i have set up sumproducts for other parts but this is all i have left. Is their a way count?

[DonkeyOte]

See my EDIT.

[Richard Buttrey]

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

Please Login or Register  to view this content.

and copied down,

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

Please Login or Register  to view this content.

HTH

[Nick_in_Dubai]

Thats great thanks, to throw one more item into the mix though, Lets say their is also a column say C that has either a yes or a no, can i join:

Please Login or Register  to view this content.

With a sumproduct so that in colum E it shows all the entries that are at 9 and are Yes? and repeated down for all the hour bands....

?? Cheers

[DonkeyOte]

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.

[Nick_in_Dubai]

Thats great thanks both...............