Grouping dates in pivot tables

F

Frank

Hello

I have a problem with grouping dates in an Excel 2000 pivot table. I
exported an Access 2000 query containing a date field into an Excel sheet. I
then made a pivot table and tried to group the date field. Normally, if you
right click on a date field, you can choose to group by day, week, quarter,
month or year. But I get the message that "the selection can't be grouped".
You should know that I didn't make a selection, I simply right click on the
date field. Also, the dates in the Excel file are "real" dates, I checked
this with the IsDate() VBA-function!

Has anyone else had the same problem? It seems to me that this problem
occurs with every Access database because I tried the same experiment with
another Access database and had the same problem.

Who has a solution for this one?
Frank
 
A

Andy B

Hi

I export queries from Access 2000 into Excel 2000. I just checked the
problem you're having and mine is OK!!
 
F

Frank Kabel

Hi
have you checked with the worksheet function
=ISNUMBER(A1)
if this returns True?. This really sounds like a problem with the
format of your data. You may try the following:
- select an empty cell and copy this cell
- select your date values
- goto 'Edit - Paste Special' and choose 'Add'

now try again creating your pivot table
 
F

Frank

Hello

I found the root of the problem: apparently if not all records contain a
date in the date field (i.e. they're left empty) then you cannot group the
date field. I filtered out all records that had no date filled in and the
grouping worked! So I just have to make sure that the date fields in my
Access db are not left empty or that I fill in a dummy date such as
1/1/1900.

Thanks for your help.

Frank
 
Top