creaet a graph from the hours of a time field

[michaelxhermes via OfficeKB.com]

Advice please

I have a column in a sheet with the time - date and time but formatted to
show the time

I have a number of tickets against each time – the time the tickets were
bought

there can be many records all for different dates

I would like to generate a simple graph showing how many tickets were
purchased in each hour period or even each 4 hour period

Can anyone please point me in the right direction please

I have looked the chart wizard and pivot tables but I cannot figure out how
to group and count the ticket numbers in to the time slots?

I hope this makes sense

thanks

Michael

 

[Pete_UK]

List the hours 0, 1, 2, .... 23, 24 somewhere (or group these in twos
or fours, but spanning from 0 to 24) - suppose you have this in column
X, with your date/times in column D and the number of tickets in
column N. Then you can put a formula like this in column Y:

=SUMPRODUCT((HOUR(D$1$2000)>=X1)*(HOUR(D$1$2000)<X2)*(N$1:N$2000))

and copy this down for as many rows as you have hour-blocks (except
for 24). This will give you a count of the number between each hour-
block.

I have assumed you have up to 2000 rows of data - adjust this if you
hve more (but you can't use a complete column prior to Excel 2007).

You can draw your graph from the results.

Hope this helps.

Pete