Date Formatting

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

Don Niall

Hi,

I am creating a pivot of a w/s consisting of 6000 rows of
records. One field in each row is the date formatted as dd-
mm-yyyy. For the pivot table I want the date formatted as
mm-yyyy only, to improve pivot reporting capabilities.
Unfortunately, it did not allow me to do this (when I
reformatted the date in ws from dd-mm-yyyy to mm-yyyy, it
has no impact on the pivot?). I created a new column and
populated this column with date in the mm-yyyy format.
This appeared to work - partly! I now discover I have
multiple pivot entries with the same month year - rather
than a single mm-yyyy category, which I would expect. Is
there some way around this?

Don-
 
Don Niall said:
Hi,

I am creating a pivot of a w/s consisting of 6000 rows of
records. One field in each row is the date formatted as dd-
mm-yyyy. For the pivot table I want the date formatted as
mm-yyyy only, to improve pivot reporting capabilities.
Unfortunately, it did not allow me to do this (when I
reformatted the date in ws from dd-mm-yyyy to mm-yyyy, it
has no impact on the pivot?). I created a new column and
populated this column with date in the mm-yyyy format.
This appeared to work - partly! I now discover I have
multiple pivot entries with the same month year - rather
than a single mm-yyyy category, which I would expect. Is
there some way around this?

Don-
Click to expand...

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.
 
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?
 
Back
Top