grouping date in pivot table

[acss]

I have a pivot table containing dates, products and amount. I have right
clicked the date field to group the dates bi-weekly since the only dates in
the column are 1/15/2009,1/31/2009,2/15/2009,2/28/2009..etc YET i receive an
error that states "cannot group that selection".....i ensured it is in date
format...what else could cause this???

 

[Roger Govier]

Hi

If there are any empty cells in the range of dates, then Excel will not
group them.
Perhaps you have defined a range for your PT which included blank rows to
allow for future data input. That would cause it not to group.

Instead, define a dynamic range
Insert>Name>Define>
Name myData
Refers to =$A$1:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))

Right click on the PT>PT Wizard>Back>Source = myData>Finish
Drag your date field off the PT>Refresh>Drag your date field back again and
try Grouping.

If that doesn't work for you, post back

 

[Ken Wright]

Besides Roger's suggestions, any cells that are text as opposed to dates
will also cause this error.

Make sure the data is clean, the range is correct, refresh, save and close
and reopen and you should be able to get past it.

Regards
Ken....................