Pivot Table Date Format Question

[Guest]

The formating of date in the spreadsheet is mmm yyyy and it does display the
way I intend at this point, but not the pivot table. When I run a report in
the pivot table a date that is supposed to show up as May 2007, shows up as
5/07/07. I even changed the format in the pivot table to the mmm yyyy
formating but it doen't work.

 

[Fred Smith]

How did you change the format in the pivot table?

You can format pivot table cells the way you do any other: select them, right
click, choose Format Cells..., specify the format you want.

 

[Debra Dalgleish]

Change the format for the field in the pivot table:

Right-click a date cell in the pivot table, and choose Field Settings
Click the Number button, and format the number field as mmm yyyy
Click OK, click OK

 

[Guest]

That is what i did, but the format still isn't displaying as I would like:
Month Year aka mmm yyyy example Jan 2007

 

[Guest]

Although your reply seems to be extremely well written... I'm not getting the
options you are suggesting... I'm running Office 2003 if that makes some
difference.
What I want to do is run the report by month. Month and categories are
displayed: 5/7/2007 instead of May 2007 the way I would like it. When I go to
the pivot table I right click on the heading "month" and choose field
settings, I don't find any number there. I did the same on the heading of
the drop down list but could't see any number botton either. Am I right in
the right cell or right menu to change the formating? Just to clarify for
you, I'm looking for that calendar format to appear in the drop down list in
the pivot table.

Thanks in advance to anyone that can help out!

 

[Debra Dalgleish]

If you don't see the number button in the field settings, there are text
entries or blank cells in the date field in the source data. Change
those to dates, and you should be able to do the number formatting.

But changing the formatting won't allow you to select all of the May
2007 dates from the dropdown list. The underlying dates will still be
the individual dates from the source data. You could group the dates, as
described here:

http://www.contextures.com/xlPivot07.html

Then put the Year and Month in the page area, and select 2007 and May.

 

[Guest]

Debra,

I'm sorry for the delay in replying. The information you gave me worked
perfectly. I do appreciate all the time and effort you've offered me in the
last week.