Chart frequency of date/time value...?

[Joe User]

This is probably simple but frankly, Excel and math are not my strong
points and never were so please, I need some help.

I have just one column that contains date/time values down the the
second, i.e.:

11/6/2004 12:00:10AM
11/6/2004 12:00:24AM
11/6/2004 12:00:27AM

I need to group this data somehow into ten or fiteen minute intervals
and bar-graph the number of rows tallied per interval.

Any help greatly appreciated!

If anyone cares, btw, the data is actually from a firewall log and I
need to determine traffic trends through the day(s).

Thanks,
-B

 

[Jon Peltier]

Joe -

This is a histogram. There are some examples here:

http://peltiertech.com/Excel/Charts/Histograms.html

Here's how you'd go about it.

Set up some times to define your intervals. For example, put 11/6/2004 12:00:00AM
into cell B1. In cell B2, put the formula =B1+TIMEVALUE("00:10:00") for ten minute
intervals. Copy cell B2, select from B3 down as far as you think you need intervals
(say B13 for 2 hours), and paste. Select C1:C14 (one more cell than you have
intervals: the last is for the count above the largest), in C1 type this formula,
=FREQUENCY(A1:A20,B1:B13), and hold CTRL+SHIFT while pressing Enter to make it an
array formula. A1:A20 is the list of times, so expand it as needed. If you do this
right, Excel puts curly brackets around the formula:

{=FREQUENCY(A1:A20,B1:B13)}

Now you can select the data in B and C and run the chart wizard to make your chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Joe User]

Great lead!!! Thanks...

Joe -

This is a histogram. There are some examples here:

http://peltiertech.com/Excel/Charts/Histograms.html

Here's how you'd go about it.

Set up some times to define your intervals. For example, put 11/6/2004 12:00:00AM
into cell B1. In cell B2, put the formula =B1+TIMEVALUE("00:10:00") for ten minute
intervals. Copy cell B2, select from B3 down as far as you think you need intervals
(say B13 for 2 hours), and paste. Select C1:C14 (one more cell than you have
intervals: the last is for the count above the largest), in C1 type this formula,
=FREQUENCY(A1:A20,B1:B13), and hold CTRL+SHIFT while pressing Enter to make it an
array formula. A1:A20 is the list of times, so expand it as needed. If you do this
right, Excel puts curly brackets around the formula:

{=FREQUENCY(A1:A20,B1:B13)}

Now you can select the data in B and C and run the chart wizard to make your chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______