Problem with date format on chart created from pivot table

[njmike]

All,

I have seen this posted here from a long time ago, but never saw an answer. Hopefully someone will be able to help.

My spreadsheet has a date formated (MMM-YY) in column A and 3 other columns of numeric data. When I create a pivot table from this data, I have to change the format of the date area in the pivot table to MMM-YY and that stays, but when I create a chart from the pivot table with the date as the horizontal axis, the format reverts to mm/dd/yy. I have tried going through the axis properties to change this, but it will not change.

I can't change the original data to text because then it will not sort correctly (month/year ascending).

Any suggestions or is this just not doable in excel?

TIA

Mike

[NikkiSB]

Sounds like you may have formatted the excel rows to the mmm-yy format rather than the pivot table field.

Click anywhere on the pivot table so that the Pivot Table Field List is displayed.
Click on the Row lable in the Field Table List that contains the date information.
Click on Field Settings and then Number format on the bottom left of the window.
Then format your Date by selecting Date in the Category and MMM-YY in the Type.
Click OK and your chart should update.

Hope this helps.

[njmike]

Thanks. I actually found the issue. Several cells were blank in the original range and that was throwing the formatting off even though it was not the date column where any of the blanks occurred. Very strange.

Thanks
Mike

[NikkiSB]

AWesome. Glad you figured it out!