Grouping Dates in Pivot tables

[Guest]

Hi All,

Although I am generally comfortable with Pivot tables I frequently run into
a problem grouping date ranges within the pivot table. Sometimes this works
fine and other times excel says that it cannot group that data. There is some
mention of this in the help however it does not suggest how to fix it. It
seems that it has to do with the date format from the original list however I
have tried reformatting and it seems to not help.

Has anyone ever figured this out? Some date ranges work fine and other
don't. Thanks for any help that you can offer.

 

[Debra Dalgleish]

That error can occur if there are blank cells or cells with text in the
date column. There are some suggestions here for fixing the problem:

http://www.contextures.com/xlPivot07.html#Problems

 

[Dave Peterson]

When I get this message, it's because I have a non-date in that column. It
could be text or an empty cell. Both of these will cause the problem you
describe.

 

[Guest]

Thanks Debra, I have tried all of these and still no luck. The data looks
good, is there any other possibility of fixing this?

Andy

 

[Guest]

Thanks Dave, I have checked this as well and tried many means of converting
to assure they are all dates. No luck yet.

Andy

 

[Debra Dalgleish]

If you don't have blank cells or text in the date column (and you didn't
include any blank rows at the bottom of the pivot table), there may be a
grouped field left over from the 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, you should be able to group the date field again.

 

[Dave Peterson]

Sometimes xl doesn't want to let go of the thought that there's non-dates in
that column.

If I have non-dates and then fixed them, I can drag the date field off the pt,
refresh the pt, and drag the field back.

It's worked in the past--I don't recall if it's 100% effective, though. But it
just worked again when I tried it.

And I'd try this to verify that you really have dates.
=counta(yourrangewithdates)
=count(yourrangewithdates)

if you get different values returned, then one of those entries is masquerading
as a date.

And both numbers should be the total number of cells in that range.

One more thought about your range for the pt. Did you include extra rows to
grow into?

If yes, then those blanks will cause the trouble. You can sometimes avoid that
resizing ranges by using a dynamic range name for your source.

Debra Dalgleish has instructions at:
http://www.contextures.com/xlNames01.html#Dynamic

 

[Guest]

You are a genius. That was it. You don't know how much this helps me. Thanks
very much.

Andy

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that was the problem.