Excel 2002 Grouping Dates in a Pivot Table 1

[planopeets]

Basically I have fields Account, Date (MM/DD/YY) and Revenue. I want to create a privot table and have the revenue show by Qtrs. I can create the Pivot table with Account on the left, Revenue as the data and Date as the Row. But when I go to select the field Date to group, I get the following error Cannot Group Selection. I've selected the first date in the Row.

I know this will turn out to be simple, thanks for any help you can offer.

 

[KenWright]

You likely have a blank or a non-real date (Perhaps Text) in the source data for that field - Find / Fix / Refresh Data / Forget.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[planopeets]

That worked. I always select the whole sheet when creating a pivot table. I guess that always places a blank in the list.
Thanks

 

[KenWright]

You're welcome. You'll also find that if you have blanks in your other data, it will default to COUNT as opposed to SUM, whereas if you fill all those blanks with 0s (selecr range, do edit / Go To / Special / Blanks, type 0 and hit CTRL+ENTER), then it will default to SUM.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[planopeets]

Yes, I had noticed that problem. I do a lot of pivot tables, always have to change count to Sum. Great. Thanks.

another question for you. I noticed that Pivot Tables Remember past data in the drop downs, even though the data doesn't exist in the new refresh, i..e pull from a text file which comes from a database. Anyway to prevent pivots tables to remembering old data values.

 

[KenWright]

Depending on which version you have, one of the bits of code from this link:-

http://www.contextures.com/xlPivot04.html#Clear2002

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------