grouping by dates in pivottable

Hi all,

My dataset contains a column of start dates / times formatted as

dd/mm/yyyy hh:mm

I have created a pivot table with this field as the column headers. I would like to group all the data by Month and year so that my column headers would read:

Jun 05 Jul 05 Aug 05

and then the data beneath these headings would be the total occurances in the month.

I have done grouping in pivottables before but not with dates and I keep getting an error box saying "Cannot group selection".

I am getting and trying to find out what the problem is cos I know that this can be done.

Any help would be appreciated.

Regards

Do you have dates in all the cells? If you have empty cells or text, then you
won't be able to group by that field.

And are you sure that your dates/times are really dates/times?

Formatting doesn't really mean much--it's the underlying value that's important.

If you put
=count(a2:axx)
(Adjust the range to match your range of dates)
do you get something different than the results of:
=counta(a2:axx)

If you do, then there is at least one cell that isn't really a date/time.


danlightbulb wrote:
>
> Hi all,
>
> My dataset contains a column of start dates / times formatted as
>
> dd/mm/yyyy hh:mm
>
> I have created a pivot table with this field as the column headers. I
> would like to group all the data by Month and year so that my column
> headers would read:
>
> Jun 05 Jul 05 Aug 05
>
> and then the data beneath these headings would be the total occurances
> in the month.
>
> I have done grouping in pivottables before but not with dates and I
> keep getting an error box saying "Cannot group selection".
>
> I am getting and trying to find out what the problem
> is cos I know that this can be done.
>
> Any help would be appreciated.
>
> Regards
>
> --
> danlightbulb
> ------------------------------------------------------------------------
> danlightbulb's Profile: http://www.excelforum.com/member.php...o&userid=23433
> View this thread: http://www.excelforum.com/showthread...hreadid=483155

--

Dave Peterson