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

[Guest]

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.

 

[Conrad Carlberg]

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.

 

[Ken Wright]

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
------------------------------­------------------------------­----------------