Group Dates in Pivot Table

[mrchonginhk]

I have a simple Pivot table just Dates plus Data.

I then right click the Date heading and tried to group dates.

However, it said "Cannot Group that Selection".
I have already removed all blank cell in Date field.

What can be wrong ??

Help !!!

 

[kojak43]

Cannot Group that selection

highlight the column of 'dates', go to data | text to columns | hit next twice | select date & DMY from the drop down | hit finished

I tried this solution. My original data was a cut and paste, so I figured that was what was causing the problem. I did not Paste Special.

This solution did show that =isnumber(ref) was false. However, after I did the quoted solution, and afterwards =isnumber(ref) was true, I still cannot not group that selection of dates.

Any guidance?

Thanks
k

 

[PaddyD]

are there any gaps in the data?

check the pivot table source range (in the wizard) - does it inadvertently include an extra, blank row at the bottom of your data?

 

[kojak43]

are there any gaps in the data?

check the pivot table source range (in the wizard) - does it inadvertently include an extra, blank row at the bottom of your data?

Sorry for the delay...
Data goes from A1:E36, No empty cells or extra rows.

When I originally made the pivot table, I selected columns A:E and they extended down to Excel limits. So, I guess I do indeed have extra rows. However, when I check the pivot table source range in the wizard, it only shows cells with data.

If I do not include the extra blank rows, refreshing does not include the newest row just input.

I can not imagine that if I want to group, I have to make a new pivot table that contains only cells with data - no blanks.

Is that correct?

 

[XLGibbs]

You can use a dynamic range to determine the size of the table by employing one of many potential methods...the Offset formula being one..

http://www.ozgrid.com/Excel/DynamicRanges.htm

excellent resource linked above, rather than retyping it....

That way you can have the pivot table contract and expand to suit the data present and not have blank rows.