Can't Group Dates in Pivot Table

[Guest]

I have a huge list(24808RX14C) forming a pivot table. For some reason, when
I right-click on the dates and go to grouping, it says "Cannot group this
selection." There is only 1 date in each month (cost collection day), but I
would like to group it by Year->Month. Any ideas on why this is happening?

 

[Guest]

BTW, I am in Excel 2003.

 

[Dave Peterson]

If you have any text or empty cells in that date field, you won't be able to
group it.

 

[Guest]

I just used a =if(isblank(A2),1,0) and drug it over an area equal to that of
the data and its sum was zero, so it's safe to assume there are no blanks
anywhere in the data.

I also multiplied the date field by 2 and then averaged the column (since
this would return a #value error if any of the dates were text) and got a
valid date back.

Therefore, I think it's safe to assume that the conditions you specified for
failure are not met.

I've also noticed that there is a (blank) option under every field-arrow in
the pivot table.

 

[Guest]

Check your range. As a guess your pivot table includes blank cells at the end
of your data range. Pull up th epivot table wizard and hit back to access the
Data Range and check that it includes only non blank data.

 

[Guest]

Jim, that was my first guess. I had originally used a named list, so I tried
converting it back to a range and defining it by $A$1:$O$24807, but I still
get the same error.

 

[Guest]

As another test, I just tried making it smaller and cut it all the way to 2
rows of data and still could not group the dates. Unless the 25 different
2-row combinations I tried were all corrupt, I have absolutely no idea what's
going on.

 

[Dave Peterson]

Using a number stored as text in a calculation (a1*2/2) will coerce the value to
a number in that calculation. So using =isnumber() would be a better test.

IIRC, I've copied the data to another worksheet and recreated the pivottable and
it's worked ok. (I think!)

Sometimes, excel seemed to remember that a cell used to be text and just
wouldn't let go--(It's been a long time, but I think that was what worked.)

 

[Guest]

I just tried the isnumber thing. I think excel might just be being stupid
with text here.

 

[Dave Peterson]

I don't know what that means.

I just tried the isnumber thing. I think excel might just be being stupid
with text here.

 

[Guest]

I used =isnumber() to check the dates, and they are all numbers. You said
earlier that you've had trouble with Excel hanging onto the text formatting,
which I think is probably the case.

 

[Dave Peterson]

Ahh.

I used =isnumber() to check the dates, and they are all numbers. You said
earlier that you've had trouble with Excel hanging onto the text formatting,
which I think is probably the case.

 

[Guest]

IIRC, I've copied the data to another worksheet and recreated the pivottable and
it's worked ok. (I think!)

I've had the same problem. Starting a brand new pivot table works, but
re-using the existing pivot table (even if you remove all the fields and
start from scratch) doesn't work.

I'm hoping there's a solution.

 

[Debra Dalgleish]

If you don't have blank cells or text in the date column, there may be a
grouped field left over from a previous time that you grouped the data.

Check the field list, to see if there's a second copy of the date field,
e.g. Date2. If there is, add it to the row area, and ungroup it.
Then try to group the dates again.