Pivot table grouping of dates in row fields will not work. Why?
 

I am trying to take a field column of dates and group them into months. The
message I get is cannot group that selection. My interpretation of help on
this subject is that it should work.

Hi Bill,

I hate when this happens.

It's usually due to a null value on the date field. Excel won't group on a
field with a null.

I suggest that you start another pivot table from scratch (because you will
already have stored a null date in the existing pivot table's cache) and
pull in the data using a criterion that avoids a null date. BTW, it's best
to start with the field that you want to group as a row field.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"Bill B" <Bill wrote in message
...
I am trying to take a field column of dates and group them into months.
The
message I get is cannot group that selection. My interpretation of help on
this subject is that it should work.

You either have Blanks or Text in one of your records in the date field.

I get this a lot when I export data from an application that has an Access
back end. There is nothing wrong with the data, it just so happens that
there is no date against a lot of the records.

As a solution, i simply select all the data in that field, do edit / Go to
/ special / blanks, and then type in the earliest date in my proposal and
hit CTRL+ENTER to enter that date into every blank. Refresh the table and
then it will group OK.

Note, this only works for me because i know my data and i know that doing
this has no impact on my particular reports, so just be sure the same is
true if you try it.

You can also use Data / Filter / Autofilter on the date field and any funny
entries will usually stand out.

Another reason this can happen is if for example 99% of your dates were
formatted as dates, but one wasn't, eg:-

01/12/05
02/12/05
03/12/05
38690

This will also prevent grouping. You would have to format the cell as date,
refersh the table and then try again.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Bill B" <Bill wrote in message
...
I am trying to take a field column of dates and group them into months. The
message I get is cannot group that selection. My interpretation of help on
this subject is that it should work