Date Formatting in Pivot tables

  • Thread starter Thread starter Don Niall
  • Start date Start date
D

Don Niall

Can anyone help with the issue outlined in the email
below? I tried the recommendation of Pivot Grouping - but
that doesn't appear to work?
Would greatly appreciate some assistance.

Thx

Don-
=========================================================

I keep getting a message indicating 'Cannot group that
selection'? I have moved the firld in question from Page
to Column and Row area - but still the same message?
-----Original Message-----
==========================================================
Simply formatting a cell has no effect on its contents. So when you format
as mm-yyyy, the underlying value still contains the day - it simply isn't
displayed. Hence each separate date still creates an entry in the pivot
table.

You need to use the Group feature in the pivot table. Click on the date
heading (to select all the dates). Then right-click and choose Group. The
Grouping dialogue box will come up, with a list of how you can group
including days, months, quarters and years. You need to select Months and
Years. (If you only select months, anything in (say) January in all years
will go into one group!) To do this, select months, then hold down CTRL
whilst selecting years.
Click to expand...
===========================================================
 
Are you sure that you don't have any entry other than a date in that field
from which the pivot table is derived? Look in the pivot table to see that
there isn't some other cell value - even a blank, or a text field - as you
will only be able to group if ALL the cells in the Column/Row area
(whichever you are using for date) are dates. When this is the case,
right-clicking any date should bring up the Grouping dialogue box.
 
Hi,

you said that you created new column to populate values as "mm-yyyy". if you
populate with '=A2' (i assume your date field in column A) and format like
"mm-yyyy" that explains why the pivot table has multiple entries on that
field. To walk around this, just use the '=TEXT(A2,"mm-yyyy")' formula on
newly created column.

Hope this helps.


Regards.


Haldun Alay
 
Back
Top