Extract and sum TIME OF DAY from column with date and time info

Hello,

I have a column of data with time and date.

I want to COUNT number of instances in each hour of the day, so I can see pattern of time of day from data.

I think I need SUMPRODUCT but the time of day is part of a string with date also which is confusing me.

In the GREEN cells on STATS is where I want to count instances occurring in each hour (and drag right)

Also, on the attached STATS sheet I can time time from and time to for each hour, but is there a formula I can put in the yellow cells and drag along to add an hour, without me typing each?

Please would someone steer me on this?

(note - have used COUNTIF perfectly for tallying by month and year, just can't figure TOD)

Thanks,

Ian

In "Data"

in B2

=MOD(A2,1)

copy down

format as hh:mm

in "Stats"

In C2

=B2+1/24

in C3

=B3+1/24

Drag across

In B4

=COUNTIFS(DATA!$B:$B,">=" &STATS!B$2,DATA!$B:$B,"<=" &STATS!B$3)

Thanks both,

@sweep - the adding an hour is perfect - thank you. The SUMPRODUCT - can it work on the times in rows 2 and 3 (so it looks for times between, including the times themselves). I also need it to work on column A of DATA as a whole, as I'll drop in a whole dataset into this sheet which is raw data I just want the STATS to update on.

@JohnTopley - thanks also. I'm needing a solution that means me NOT tweaking the DATA tab at all - due to the note above. I've used column A on my example but in the actual data - there are columns before and after (the time/date is actually in column Z of a much bigger set of info).

Put the time following the Date/Time: with 16K columns to use ....

....and I don't want to do any changes or tweaks at all on the DATA tab, so that method won't work for me, I'm sorry. I may be able to have a second sheet pulling the data from the DATA tab to then add your time column, as a workaround. Let me try that now.

You only need add the formula once into DATA: how is DATA normally populated?

I get a data dump of over 50 columns, and drop the entire thing in - each time, there are more rows for most recent engagements.

Other data pulls from this so changing it in any way is not an option. Just working on pulling data elsewhere to then modify time and implement your solution and test.

Interestingly - both solutions work BUT Johns solution returns 5 fewer records on the PM row - when the formula is populated over the whole data (attached).

It's an acceptable margin for error and while both require some tweaking to work on the entire column on time/dates, I do now have what's needed for analysis.

Thanks both.

Ian

posted in wrong thread, sorry