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.
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 [email protected]> wrote in message
news:[email protected]...
> 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 [email protected]> wrote in message
news:[email protected]...
>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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks