Group on date field in pivot table

[William Benson]

I cannot group on a date field in XL2002. I select the field header, and
Excel says I cannot group on it. I then select all the data elements, and
it makes one big group instead of treating the field as "date." The source
data is an Excel spreadsheet, and the data is formatted as date both at the
source and it the pivot table.

Anyone know why this happens or how I can resolve the problem?

 

[Debra Dalgleish]

If there are blank cells, or cells with text, in the date column, you
won't be able to group on that field.

 

[William Benson]

Debra -

Thank you very much. I have a formatted sheet that I used for the source
data, and it has multiple rows at the top for column headers. Also, I have
only 34 rows of data but will add to it each day. So I used as the pivot
table source data something like "A3:F9999," which includes thousands of
blank rows. I redefined the source data to include only populated rows, and
it worked great.

The grouping also failed when I selected entire columns. So, I guess the
only option for me is to refresh the source data every time or to store the
data in a database.

Thanks very much -- it would have driven me crazy, and microsoft offers no
help on this particular topic.

 

[Debra Dalgleish]

You're welcome. You can use a dynamic range as the source for the pivot
table, and it will expand automatically as new rows are added. There are
instructions here:

http://www.contextures.com/xlPivot01.html

 

[William Benson]

Wow - thank you again. This is exactly what I need.