Grouping Dates in Pivot tables

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.

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

Andy M wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

Andy M wrote:
>
> 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.

--

Dave Peterson

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

"Debra Dalgleish" wrote:

> 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
>
> Andy M wrote:
> > 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
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

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

Andy

"Dave Peterson" wrote:

> 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.
>
> Andy M wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

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.

Andy M wrote:
> 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
>
> "Debra Dalgleish" wrote:
>
>
>>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
>>
>>Andy M wrote:
>>
>>>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
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

Andy

"Debra Dalgleish" wrote:

> 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.
>
> Andy M wrote:
> > 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
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>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
> >>
> >>Andy M wrote:
> >>
> >>>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
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

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

Andy M wrote:
>
> Thanks Dave, I have checked this as well and tried many means of converting
> to assure they are all dates. No luck yet.
>
> Andy
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > Andy M wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

Andy M wrote:
> You are a genius. That was it. You don't know how much this helps me. Thanks
> very much.
>
> Andy
>
> "Debra Dalgleish" wrote:
>
>
>>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.
>>
>>Andy M wrote:
>>
>>>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
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>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
>>>>
>>>>Andy M wrote:
>>>>
>>>>
>>>>>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
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html