Prev: filter a column from other sheet.
Next: autofill timestamp
From: Duncs on 7 Jul 2008 08:32
All,

I don't know if this is possible, but here goes.

I have a number of entries in a spreadsheet, showing me date / time of
access and a site. For example:

01-07-2008 09:15:34 Aberdeen
01-07-2008 09:15:37 Aberdeen
01-07-2008 10:10:10 Glasgow
etc.

What I need to show is a graph that lists the number of users, per
site and per hour that accessed the system. So, in the above example,
two columns / entries would be shown on the graph with Aberdeen having
a count of 2 for the period 09:00 until 09:59 and Glasgow showing 0.

Then, in the second column, Aberdeen would show 0 for the period 10:00
until 10:59 and Glasgow would show one.

Is this possible?

TIA

Duncs
From: BobT on 7 Jul 2008 09:36
You first need to do a grouping (a.k.a. subtotal) of the data. Once that is
done (with a Count on the time range - which needs to be formatted to allow a
group by hours (i.e. drop the mintes)) then you can chart away and show
bars/lines per region by the hour.

"Duncs" wrote:

> All,
>
> I don't know if this is possible, but here goes.
>
> I have a number of entries in a spreadsheet, showing me date / time of
> access and a site. For example:
>
> 01-07-2008 09:15:34 Aberdeen
> 01-07-2008 09:15:37 Aberdeen
> 01-07-2008 10:10:10 Glasgow
> etc.
>
> What I need to show is a graph that lists the number of users, per
> site and per hour that accessed the system. So, in the above example,
> two columns / entries would be shown on the graph with Aberdeen having
> a count of 2 for the period 09:00 until 09:59 and Glasgow showing 0.
>
> Then, in the second column, Aberdeen would show 0 for the period 10:00
> until 10:59 and Glasgow would show one.
>
> Is this possible?
>
> TIA
>
> Duncs
>
From: Max on 7 Jul 2008 09:36
One play - use a helper col to extract the hour from the Time col,
then create a pivot table

Data is assumed split into 3 cols: Date, Time, Loc in cols A to C
from row2 down

In D1: Hour (a label)
In D2, copied down: =IF(B2="","",HOUR(B2))
Then create a pivot table on cols A to D, drag n drop "Loc" in ROW & in
DATA, "Hour" in COLUMN, to get the desired results

A quick sample for the above:
http://www.freefilehosting.net/download/3j9ll
Extract Hour n Pivot.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Duncs" wrote:
> I don't know if this is possible, but here goes.
>
> I have a number of entries in a spreadsheet, showing me date / time of
> access and a site. For example:
>
> 01-07-2008 09:15:34 Aberdeen
> 01-07-2008 09:15:37 Aberdeen
> 01-07-2008 10:10:10 Glasgow
> etc.
>
> What I need to show is a graph that lists the number of users, per
> site and per hour that accessed the system. So, in the above example,
> two columns / entries would be shown on the graph with Aberdeen having
> a count of 2 for the period 09:00 until 09:59 and Glasgow showing 0.
>
> Then, in the second column, Aberdeen would show 0 for the period 10:00
> until 10:59 and Glasgow would show one.
>
> Is this possible?
>
> TIA
>
> Duncs
>
From: Duncs on 7 Jul 2008 17:45
Thanks to all for your replies. I've got it sorted now.

Duncs

On Jul 7, 2:36 pm, BobT <B...(a)discussions.microsoft.com> wrote:
> You first need to do a grouping (a.k.a. subtotal) of the data.  Once that is
> done (with a Count on the time range - which needs to be formatted to allow a
> group by hours (i.e. drop the mintes)) then you can chart away and show
> bars/lines per region by the hour.
>
>
>
> "Duncs" wrote:
> > All,
>
> > I don't know if this is possible, but here goes.
>
> > I have a number of entries in a spreadsheet, showing me date / time of
> > access and a site.  For example:
>
> > 01-07-2008 09:15:34     Aberdeen
> > 01-07-2008 09:15:37     Aberdeen
> > 01-07-2008 10:10:10     Glasgow
> > etc.
>
> > What I need to show is a graph that lists the number of users, per
> > site and per hour that accessed the system.  So, in the above example,
> > two columns / entries would be shown on the graph with Aberdeen having
> > a count of 2 for the period 09:00 until 09:59 and Glasgow showing 0.
>
> > Then, in the second column, Aberdeen would show 0 for the period 10:00
> > until 10:59 and Glasgow would show one.
>
> > Is this possible?
>
> > TIA
>
> > Duncs- Hide quoted text -
>
> - Show quoted text -

 | 
Pages: 1
Prev: filter a column from other sheet.
Next: autofill timestamp