Per hour-of-day summary chart

Is there an easy way to create a chart showing the total number of
requests per hour of day from the following raw data (simplified
example, real data consists of thousands of log entries)?

Timestamp | Number of transactions
===============================================
2006-05-01 1:15:54 pm | 41
2006-05-01 1:15:55 pm | 33
2006-05-01 1:15:56 pm | 40
2006-05-01 1:18:08 pm | 3
2006-05-01 3:40:12 pm | 1
2006-05-01 8:01:33 pm | 2
2006-05-02 1:52:04 am | 1
2006-05-02 2:01:33 pm | 1
2006-05-03 3:11:11 pm | 1

What about I'd like to categorize the transactions in the chart by
using a third column in the raw data called "type of transaction"?

I've been trying to Google around but apparently am not savy enough
with Excel to even come up with the correct keywords...

TIA,
Keke

If those values in the Timestamp column are really dates/times, you could use a
pivottable.

If they're not really dates/times, then I'd convert them to dates/times.

Try this against a copy of your worksheet
select the Timestamp column
data|text to columns
fixed width
But remove any lines and don't add them yourself.
and finish up the wizard.

When I did this, excel was smart enough to convert the data into real
dates/times.

Then I could select my range--both columns (timestamp and transaction count)
data|Text to columns
follow the wizard until you get a step with a button with Layout on it.
click that Layout button
drag the timestamp header to the row field
drag the Number header to the data field
and finish up the wizard.

Now rightclick on any entry with a date/time (in column A)
choose Group and show detail
Then choose Group
Group by Days and hours.

=======
When you get the third column, you can drag that "type" to the column field (or
to the row field below the timestamp) to see how the report would "pivot".

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

Keke wrote:
>
> Is there an easy way to create a chart showing the total number of
> requests per hour of day from the following raw data (simplified
> example, real data consists of thousands of log entries)?
>
> Timestamp | Number of transactions
> ===============================================
> 2006-05-01 1:15:54 pm | 41
> 2006-05-01 1:15:55 pm | 33
> 2006-05-01 1:15:56 pm | 40
> 2006-05-01 1:18:08 pm | 3
> 2006-05-01 3:40:12 pm | 1
> 2006-05-01 8:01:33 pm | 2
> 2006-05-02 1:52:04 am | 1
> 2006-05-02 2:01:33 pm | 1
> 2006-05-03 3:11:11 pm | 1
>
> What about I'd like to categorize the transactions in the chart by
> using a third column in the raw data called "type of transaction"?
>
> I've been trying to Google around but apparently am not savy enough
> with Excel to even come up with the correct keywords...
>
> TIA,
> Keke

--

Dave Peterson

Dave Peterson wrote:
> If those values in the Timestamp column are really dates/times, you could use a
> pivottable.

Thanks Dave, worked like a charm!

I have a block of data with dates and times in one column and call catergorys in the next plus other columns of info too. I initially need to count all entries in 15 min blocks, for each day in a data block which can cover days, weeks, months so it has to be flexible.


examples

eg. from 1/01/2006 00:00:00 AM (midnight) to 1/01/2006 00:15:00 AM then 1/01/2006 00:15:00 am to 1/01/2006 00:30:00 AM and so on and so on are the time zones

That makes a summary of 97 sub totals perday, the next step is to also subtotal call types aginst each entry in these 15 mins time zones.

eg. from 1/01/2006 00:00:00 AM to 1/01/2006 00:15:00 AM =10 entries.
and there are say ....
2 catergory type 1 entries / 3 catergory type 2 entries / 2 catergory type 3 entries / 3 catergory type 4 entries.

I have tried many differnrt ways but Im not up to creating d functions for all these criterias in excel there must be a easy way.

Many Thanks

left click on your time axis> axis type> Text Axis. What up!!