Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Andy M
 
Posts: n/a
Default 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.
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #4   Report Post  
Andy M
 
Posts: n/a
Default

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


  #5   Report Post  
Andy M
 
Posts: n/a
Default

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



  #6   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #8   Report Post  
Andy M
 
Posts: n/a
Default

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


  #9   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot tables - inserting columns Scott Excel Worksheet Functions 1 March 19th 05 02:07 AM
Blank Cells in Pivot Tables Greg Excel Discussion (Misc queries) 1 March 16th 05 10:23 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM
Product Function in Pivot Tables from Multiple Consolidation Range bbishop222 Excel Worksheet Functions 0 February 22nd 05 05:55 PM
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. stvermont Excel Discussion (Misc queries) 1 February 17th 05 02:34 AM


All times are GMT +1. The time now is 04:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"