Can't Group Dates in Pivot Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
If you have any text or empty cells in that date field, you won't be able to
group it.
 
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.
 
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.
 
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.
 
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.
 
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.)
 
I just tried the isnumber thing. I think excel might just be being stupid
with text here.
 
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.
 
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.
Click to expand...
 
Dave Peterson said:
IIRC, I've copied the data to another worksheet and recreated the pivottable and
it's worked ok. (I think!)
Click to expand...


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.
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot ungrouping on refresh? 3
Pivot Table - Group by Date Excel 2007 1
Grouping daily transactions in a pivot table by month - CANNOT GROUPTHAT SELECTION ERROR 1
Group in Multiple Pivot table 1
Grouping in a Pivot Table 1
Pivot Table and Grouping Problem 3
Pivot table date grouping 2
grouping multiple tables Excel 2007 3

Back
Top