Pivot Table Date Format Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Titanium - 17 Jun 2007 23:45 GMT

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.

Reply to this Message

Fred Smith - 18 Jun 2007 00:25 GMT

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.

Signature

Regards,
Fred

> 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.

Reply to this Message

Titanium - 18 Jun 2007 00:46 GMT

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

> How did you change the format in the pivot table?
>
[quoted text clipped - 6 lines]
> > 5/07/07. I even changed the format in the pivot table to the mmm yyyy
> > formating but it doen't work.

Reply to this Message

Debra Dalgleish - 18 Jun 2007 00:39 GMT

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

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Titanium - 18 Jun 2007 01:06 GMT

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!

> Change the format for the field in the pivot table:
>
[quoted text clipped - 7 lines]
> > 5/07/07. I even changed the format in the pivot table to the mmm yyyy
> > formating but it doen't work.

Reply to this Message

Debra Dalgleish - 18 Jun 2007 04:26 GMT

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.

> 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
[quoted text clipped - 21 lines]
>>>5/07/07. I even changed the format in the pivot table to the mmm yyyy
>>>formating but it doen't work.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Titanium - 20 Jun 2007 22:19 GMT

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.

> 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
[quoted text clipped - 34 lines]
> >>>5/07/07. I even changed the format in the pivot table to the mmm yyyy
> >>>formating but it doen't work.

Reply to this Message