Problem grouping dates

[yellowcabguy]

Having another senior moment.

Have a pivot that has as one of its columns -dates (1/2/06; 3/4/06, etc)

When I try to group the dates into months I get an error message "Can not group that selection"

Any guidance would be appreciated

 

[Dufus]

yellowcabguy,

Is the "date" an Excel date serial number or text entered to look like a date?

Dufus

 

[PaddyD]

or blanks in the data.

 

[yellowcabguy]

problem sorting dates

Hi,
There are no blanks and the format of the column is DATE displayed as 3/16/2006. Where else should I look??

 

[yellowcabguy]

This is how the data is displayed in the pivot

Status Date Count

Adopted 1/2/2006 2
1/7/2006 2
1/12/2006 1
1/21/06 2
1/22/2006 1
1/28/2006 3
1/30/2006 1

 

[Richard Schollar]

What's the last row of data in your pivot table source data range, and what do you have defined as the PT source data range in the PT wizard? This is basically checking that you have no blanks in the range as PaddyD suggested.

Richard

 

[yellowcabguy]

grouping dates

I think I figured out what I was doing wrong.

BEFORE I was trying to group I unsleceted one of the categories of data (Column). Excel would not let me group the dates. When I included all the categories and tried to group is worked fine. I then unselected the column of data I did not want to see and what was left was the data I needed grouped correctly.

Does that make any sense??

 

[yellowcabguy]

This is the referneced defined name range I am using to refresh my pivot could this be causing the problem in that the refesh refernece is finding blanks??

=OFFSET(Inventory!$A$3,0,0,COUNTA(Inventory!$A$3:$A$65536),90)

 

[Richard Schollar]

Hi

That will depend on if you have any blanks in the data range (which I believe you have already confirmed that you do not) or if you have any additional information contained in cells in the A column actually below the data range (for example, if you had a note written below the data, or even if you had a rogue full stop for example in any cell) which would then be included within the COUNTA function and thus extend the dynamic name beyond where it should end.

If you think you don't have anything below your data range, it might be a good idea to select the first empty cell below your data, and extend this selection to the last row ie so An:A65536 where n is the first blank row, and go Edit>Clear>Contents just to make sure. Hopefully this will allow you to Group without problems.

Richard

 

[yellowcabguy]

Grouping Error

I have either text or blanks in Ros 1, 2 & 3. The data actaully starts in row 4. Nothing below the last row of data does this help??