Generate meaningful graph for times of day in Excel?

Question

I have an excel spreadsheet, specifically it's a list of hardware devices with various details dumped out of our system. Each row has a 'last modified' time/date value in the format of 09/09/2006 10:37 etc

I want to be able to create a graph where I can see which times of the day most changes occured?

The table basically looks like this:

Address      Subaddress	Active	Created             Modified            ModifiedBy
10.96.48.58  2003	    no 	    04/05/1997 15:58	14/09/2006 10:37	djc2
10.209.35.34    2009       yes     16/05/1997 18:01	   06/07/2009 16:08	   vm5

So this means the graph will have to ignore the date pretty much and only look at the time, with the time of day along the x axis and number of changes on the y axis. Can this even be done easily in Excel 2002?

Answer 1

Create a new column called "Time of Day" with this formula (assuming mod times start in A5):

=MROUND(A5-INT(A5),TIME(0,10,0))

(Why doesn't Excel have a FRAC function?)

This rounds the times to 10 minute intervals. You can change that to some other value (e.g. 5 or 15). Copy the formula to all rows.

Select the whole sheet and do a sort on the new column.

Now select the cells of the new column and do a subtotal, choosing "count" as the function. You can probably leave the other settings the way they are.

Now, on the left side of the sheet you'll see outline markers. Click on the second column. This should collapse the sheet to show only the counts. You can do a search and replace to make the column that the subtotal operation creates have text that you like. I replaced "Count" with nothing and it left just the times (which will become the X-axis labels).

Now select the count column and the Time of Day column, omitting the grand total at the bottom, and create a column chart.

I'm using Excel 2007, so I'm not being more detailed in my instructions since the user interface is so different from Excel 2002, but hopefully this will get you started.

Answer 2

Seems like you could create a new column that contained the date modulus one day and then graph against that. This works in OpenOffice:

=MOD(A5;TIME(24;0;0))

Assuming that your date is in A5. You'll probably end up needing to format the cell as a date. It's probably going to end up displaying it as a fraction less than one by default.