Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Date Format Question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Date Format Question
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. -- Regards, Fred "Titanium" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Date Format Question
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 Titanium wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Date Format Question
That is what i did, but the format still isn't displaying as I would like:
Month Year aka mmm yyyy example Jan 2007 "Fred Smith" wrote: 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. -- Regards, Fred "Titanium" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Date Format Question
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" wrote: 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 Titanium wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Date Format Question
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 he http://www.contextures.com/xlPivot07.html Then put the Year and Month in the page area, and select 2007 and May. Titanium wrote: 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" wrote: 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 Titanium wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Date Format Question
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. "Debra Dalgleish" wrote: 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 he http://www.contextures.com/xlPivot07.html Then put the Year and Month in the page area, and select 2007 and May. Titanium wrote: 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" wrote: 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 Titanium wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format a Grouped Date in a Pivot Table | Excel Worksheet Functions | |||
Format Date in Pivot Table | Excel Discussion (Misc queries) | |||
Date in Pivot table question | Setting up and Configuration of Excel | |||
Pivot Table Date Format | Excel Discussion (Misc queries) | |||
Pivot Table Question: Show percentages and standard number format | Excel Discussion (Misc queries) |