PivotTable question - date format

[-= cj =-]

Hi

Am newbie with PivotTables & having annoying problem with dat
formats.
My raw data table has dates entered in mm/dd/yyyy which I have formate

cells to show as mmm-yy.

However in the Pivot Table, how do I get the dropdown list for th
dates
to show mmm-yy instead of the mm/dd/yyyy format? I need to get data
by selecting months, not each individual date.

For some strange reason for various tables, sometimes the dropdow
list
seems smart enough to change all the dates to mmm-yy format by itself

other times it lists all zillion & ten dates in the mm/dd/yyyy format
Can't
figure out how why.

If someone could point me in the right direction it would be much
appreciated. Thanks in advance!

C

 

[Debra Dalgleish]

You can group the dates in the pivot table --

Right-click on the Date field button
Choose Group and Show Detail > Group
Select Year and Month
Click OK

Or, create a column in the source table in which you calculate the year
and month of the date. For example, with the first date in cell A2,
enter the following formula in cell B2: =TEXT(A2,"yyyy mm")

Or calculate each date to the first of the month:
=DATE(YEAR(A2),MONTH(A2),1)

Copy the formula down to the last row of data.
Refresh the pivot table, and add the new field in the row area.