Charting number of contacts a day

[Sean McCloskey]

So here's what I am trying to do, I have a source table with the following
entries:

00006 - Main Support Closed m 1/12/06 11:44 AM
00006 - Main Support Closed m 1/12/06 11:27 AM
00006 - Main Support Closed m 1/10/06 10:41 AM


Two columns. What I want to do is create a chart where it shows for example
Jan 12, there were 2 entries, and Jan 10, there were 1 entry. Ofcourse this
has to be scalable to a larger set. I just want to see the trend of entries
per day.

I don't want a bunch of =Countif's if possible. Ideas?

 

[Jon Peltier]

Assuming your two columns are Description and Date-Time, you can use a pivot
table to do this for you. select a cell in the range, and choose PivotTable
and PivotChart Report from the Data menu, and select some reasonable
options. Drag the Date-Time field into the Data area of the empty pivot
table, then drag the field again into the Rows area. Without doing anything
further, the pivot table looks like this:

Sample Raw Data:

what when

a 1/15/2006

b 1/15/2006

c 1/15/2006

d 1/14/2006

e 1/14/2006

f 1/13/2006

g 1/13/2006

h 1/13/2006

i 1/12/2006

Pivot Table:

Count of when

When Total

1/12/2006 1

1/13/2006 3

1/14/2006 2

1/15/2006 3

Grand Total 9


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

 

[Lawrence]

I'm facing a similar problem, and Jon, I find a pivot table to get me
very close to where I want to be, except that, like Sean, I have a
dates field that looks like this:

1/12/06 10:20 AM
1/12/06 11:44 AM

So two entries for January 12th, but different times. Now I can format
the cells to show only the dates, but when I use a pivot chart, it sees
both those rows as different, and will create a total of 1 for 10:20 am
and one for 11:44 am. How do I get the pivot chart to ignore the time
and only pay attention to the date?

 

[Jon Peltier]

This is the Grouping feature I described in my last post. Here's another
example:

Dummy data:

Time
1/12/2006 10:20
1/12/2006 11:44
1/12/2006 13:08
1/12/2006 14:32
1/13/2006 10:20
1/13/2006 11:44
1/14/2006 10:20
1/14/2006 11:44
1/14/2006 13:08
1/14/2006 14:32
1/14/2006 15:56
1/14/2006 17:20
1/14/2006 18:44
1/14/2006 20:08
1/14/2006 21:32
1/14/2006 22:56

Make a pivot table, with the Time field in the Row area and in the Data
area:

Count of Time
Time Total
1/12/2006 10:20 1
1/12/2006 11:44 1
1/12/2006 13:08 1
1/12/2006 14:32 1
1/13/2006 10:20 1
1/13/2006 11:44 1
1/14/2006 10:20 1
1/14/2006 11:44 1
1/14/2006 13:08 1
1/14/2006 14:32 1
1/14/2006 15:56 1
1/14/2006 17:20 1
1/14/2006 18:44 1
1/14/2006 20:08 1
1/14/2006 21:32 1
1/14/2006 22:56 1
Grand Total 16

Right click on the Time field button, choose Group and Show Detail, then
choose Group. Unselect Months, and select Days:

Count of time
time Total
12-Jan 4
13-Jan 2
14-Jan 10
Grand Total 16

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