Making a Graph that shows Frequency based on time

Originally Posted by jamboc

This is what I am trying to do.

I want to produce a column graph that on the X axis shows time slots for a 24 hour period i.e 00:00 to 00:59, 01:00 to 01:59 etc with the Y axis then showing each occurrence in each time slot.

The data I have is a list of times, e.g 11:10 11:49 14:32. So what I want is for excel to first count the number of occurrences from these times that they fall into a particular time slot, so from the data above there are 2 entries that would fall into the 11:00 to 11:59 slot and so on the graph would show 2 units in that slot and one unit in the 14:00 to 14:59 entry.

The idea for the graph is to see if a particluar time of the day is when something occurrs more often, but it dosent need to be quite specific to the minute more to the hour, but the data gained contains the time in full and needs to be kept this way for other reasons aswell.

Any ideas or examples would be greatly appreciated.

Regards

James

The attached was setup recently to do this over a days period, if your data is for one day only then you would need columns B and C only and reduce the SumProduct accordingly to (in C3, then formula-fill)

=SUMPRODUCT(--(MOD($A$3:$A$53,1)<$B3)*(--(MOD($A$3:$A$53,1)>=$B2)))

Hope this helps
---