X Axis Date Labels from Pivot Chart

[utes99]

I have a stacked column chart from a pivot table and I need the X axis to contain labels in "MMM-YY" format, so Aug-11, for example, but all I can get is the x-axis labels in mm/dd/yyyy format.

I went into the axis options and changed the format to what I want, but it doesn't apply the change to the graph. Ironically I've got another pivotchart in the same workbook (different data source), where the "MMM-YY" format is working.

How can I make the x-axis labels for dates display in "MMM-YY" format?

Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

You haven't said what version of Excel you are using.

By default the x axis for a column chart is a category axis rather than a value axis, so you can't apply a number format. If you group the date field by month and year do you get what you want?

 

[Jon Peltier]

In addition to Andrew's suggestion, what if you format the date range in the pivot table. Does this format follow through to the chart.

 

[utes99]

Gents,

I appreciate your replies.

I'm using 2007.

Unfortunately, I tried all of your suggestions prior to posting, and no luck.

In the end, I was forced to hard-code the pivot table and make the chart off of the hard data. This worked, but in the end this is an unfortunate solution because I feel like I cheated Excel, and until I figure this puppy out will be forced to do so each time I make this chart.

Again, thanks for the efforts to help.

 

[leongmay112]

Following on this thread, I am still not able to modify the dates ie to remove the year from display using the instructions in this thread.
I am using Excel 2010.
The graph is created using pivot table and the cell has dd/mm/yyyy - dd/mm/yyyy. The date range is created using the pivot function. After creating the graph the axis is displaying the values as found in the cell.
I would like to remove the year in the display of the x axis ie to display as dd/mm.

 

[Jon Peltier]

As soon as the cell contains something that's not just a simple, single date, you can no longer apply any date formatting to it.

Sometimes I will use a blank column next to the column containing "dd/mm/yyyy - dd/mm/yyyy" entries. I usually put either the first of the dates in this column, as a date, or the midpoint of the two dates, but you could also put just the "dd/mm - dd/mm" entries here.