Problem grouping dates

yellowcabguy

Active Member
Joined
Dec 28, 2005
Messages
440
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
yellowcabguy,

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

Dufus
 

yellowcabguy

Active Member
Joined
Dec 28, 2005
Messages
440
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

Active Member
Joined
Dec 28, 2005
Messages
440

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Dec 28, 2005
Messages
440

ADVERTISEMENT

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

Active Member
Joined
Dec 28, 2005
Messages
440
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Active Member
Joined
Dec 28, 2005
Messages
440
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??
 

Forum statistics

Threads
1,141,487
Messages
5,706,663
Members
421,459
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top