Make a pivot table. First, format the Date column as dates, to give the PT a
hint. Select the data, choose Pivot Table Report from the Data menu. Drag
the Date field to the Row area of the empty PT frame, then drag it again to
the Data area. You have a pivot table that looks like this:
Count of Date
Date Total
7/22/05 7:29 1
7/22/05 9:37 1
8/3/05 13:25 1
8/5/05 6:04 1
8/8/05 6:41 1
8/8/05 7:06 1
8/8/05 7:17 1
8/9/05 11:16 1
8/10/05 10:43 1
8/15/05 9:21 1
8/16/05 14:13 1
8/17/05 11:22 1
8/23/05 6:59 1
Grand Total 13
Right click on any of the dates, and choose Group and Show Detail, then
Group. Select Days and unselect Months, change Starting At and Ending At to
integral dates (not date-times), and use any date outside of the range, far
outside to give yourself room for the data to expand. Your table now looks
like this:
Count of Date
Date Total
22-Jul 2
3-Aug 1
5-Aug 1
8-Aug 3
9-Aug 1
10-Aug 1
15-Aug 1
16-Aug 1
17-Aug 1
23-Aug 1
Grand Total 13
This would make a nice histogram. Make a column chart or a line chart, in
either case using a time scale axis.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
wrote in message
ups.com...
I have a query in Excel that returns a list of events and what day/time
they occured. I use filter to get to the shift - type - subcategory i
want. I end up with a list like below.
Defect Type Sub-Category Date Shift Line Glass
Type
11 In Press 38555.31221 1 3 2
11 In Press 38555.40115 1 3 2
11 In Press 38567.55948 1 3 2
11 In Press 38569.253 1 3 2
11 In Press 38572.279 1 3 2
11 In Press 38572.29608 1 3 2
11 In Press 38572.30359 1 3 2
11 In Press 38573.46954 1 3 2
11 In Press 38574.44715 1 3 2
11 In Press 38579.38991 1 3 2
11 In Press 38580.59279 1 3 2
11 In Press 38581.47425 1 3 2
11 In Press 38587.29119 1 3 2
The dates are the Excel serial numbers for the dates and times of each
event in the query. I want to chart the count of events for each day in
the list. So for day 38555 there are 2 events that occured. I would
like to have a X-Y chart that has the dates converted into 7/22/05
format and show a Y value of 2 for that count of events on that day.
This is driving me crazy on how to work with these date serial numbers
and sum them up into something that is chartable. One of my problems is
that the solution has to be dynamic so that when I refresh the data
query the charting will update accordingly.
I would appreciate any help someone can give me on this problem
Scott