Formatting pivot chart axis labels for dates

[dsm]

If I have a pivot chart with dates on the x-axis, I am unable to "number"
format the axis. My source data has dates formatted to "Mar-09", but the axis
shows "3/1/2009". If I format the dates in the pivot table they look fine,
but the chart still shows "3/1/2009". I believe with 2003 I could format the
dates in the table and they would be reflected in the charts. If I use format
axis on a pivot table axis it has no affect at all.

This seems like a glaring hole for producing business charts so I must be
missing something new.

Steve

 

[dsm]

Sorry I should I have mentioned this is excel 2007 with SP 2. As you might
infer from the note below I know how to do this on excel 2003, but on 2007 it
doesn't work.

 

[nav]

I have a pivot chart with dates on the x-axis, I am able to format "Date"field in one work book but not in the other one as don't see "Number" Tab below "Layout" Tab in Pivot Table Field in other workbook"
WHat could be the reason??

 

[Shane Devenshire]

Hi,

Right-click the axis on the pivot chart and choose Format Axis, Number tab,
Date...

 

[Shane Devenshire]

Hi,

Sorry about that last post, the steps are correct but there is no result,
however, I found one that works -

With the chart selected and the Pivot Table Field List displayed click the
Date field in the Axis Fields area to open the list and choose Field
Settings, Number Format, ...

 

[dsm]

The drop down menu in the Axis Fields area for this field brings up a dialog
which includes two tabs: "Subtotals & Filters" and "Layout & Print". Neither
of these has a number format control that I can find...

 

[Shane Devenshire]

Hi,

You need to be in the Axis Field area of the PivotTable Field List, not the
PivotChart Filter Pane. You should see a drop down arrow which when you open
it there should be a Field Setting option, pick it. At the bottom of the
dialog box, left side same row as the OK and Cancel button, should be the
Number Format button. If this is missing it suggests that your dates are not
dates in the spreadsheet, they need to be.

 

[dsm]

Ok, got it to work. I had some blanks in the time series data that prevented
that button from coming up. After I eliminated the blanks when I clicked on
the button Excel would crash though. This was very repeatable. But I was
encouraged that this was the answer so I deleted all my pivot tables and
recreated them. After doing this, the formatting from the raw data actually
came through on the chart so I didn't have to use the button anyhow. I guess
the blanks polluted something that prevented this from working.

thanks!

 

[SKR]

Hello DSM,

Can you tell me how to get this in Excel 2003 sp3. I am still confused as to
what is axis area, filter area.

I am stuck with this since some time and spend lot of time trying to edit
using MS Paint. If i can do this thru pivot it saves 20 minutes of my time
spent.

Thanks in Advace,
SKR

 

[SKR]

Hi Shane,

Can you be abit more clear regarding the Axis area. With the chart selected
and the Pivot Table Field List displayed I did select the Date filed and
double clicked the Date

Date field in the Axis Fields area to open the list and choose date button Field, but it displays only the filters and no where i see the number field to change the format of the date.

Could you please elaborate the explanation and make me clear. I am very new
to Advanced usages of Microsoft Excel.

Thanks,
SKR

 

[SKR]

Hi,

I could figure out the solution after playing around for sometime. I had
some blank fields in the data so could not get the date format as it was in
pivot table.

Still your solution helped me in figuring out the solution.

Regards,
SKR