Using the "group and show detail" in a pivot table

[Roachie]

I am in a pivot table and want to group my dates (i.e. 1/1/01, 3/4/01) by
month (i.e. January, February). When I right click on the date column, then
click "group and show detail" then "group" I get a message saying "cannot
group that selection". I have used this feature in the past with success,
but I am wondering if there is something in my source data preventing it from
grouping.

Can anyone help?

 

[Fred Smith]

It means you have cells in that column that aren't dates. Maybe there's
blanks, or text. Ensure all your cells are dates, then the error message
will go away.

Regards,
Fred

 

[Roachie]

Fred,

I reformated the column with a date format, and filtered on it to check for
"blanks". It didn't show any blanks, however, when I refreshed the pivot
table and filtered on the date column, it showed "blank" at the end of the
list. What can I be doing wrong.

Thanks for your first response.

 

[Fred Smith]

Did your refresh specify a new range which now includes blanks?

The other thing I would check for is that your dates are actually Excel
dates, rather than text masquerading as a date. When you change the format,
does the display change? If not, you have text, not dates, and will need to
convert them to dates first.

If you want, you can send the file to fred dot smith at shaw dot ca, and
I'll take a look at it for you.

Regards,
Fred