none
Date Format Not Updated on Pivot Chart X Axis RRS feed

  • Question

  • I am using Excel 2007.

    I am consistently seeing this issue on every Pivot Chart created.

    I am using dates on the X Axis (horizontal axis) and want to format them to DDD-YY.

    I have done the following steps:

    1. Right-clicked on the axis labels
    2. Clicked on "Format Axis..."
    3. Clicked "Number"
    4. Clicked "Date", "Mar-01"
    5. Clicked Close button

    RESULT: No Change to date format in chart labels on X Axis, still dated in m/d/yyyy format.

    I have also done the following steps:

    1. Right-click a date in the pivot table (not the pivot chart).
    2. Click on Field Settings
    3. Change the Number Format to the date format that you want.

    RESULT: Pivot Table format changed successfully, but No Change to date format in chart labels on X Axis, still dated in m/d/yyyy format.

    :(

    Thursday, February 11, 2016 10:17 PM

All replies

  • Hi RitaMAC,

    According to your description, this issue is related to Excel, the Excel IT Pro Discussions forum is the better place for Non-programming issue and we will move it there for you.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, February 15, 2016 6:09 AM
  • Hello,

    The pivot chart date formats are controlled by the field format in the pivot table. You cannot change the date format like in regular charts. Instead follow this approach:

    Right-click a date in the pivot table (not the pivot chart).
    Click on Field Settings
    Change the Number Format to the date format that you want.

    This has been posted by Debra Dalgliesh on the Contextures blog.


    cheers, teylyn

    Monday, February 15, 2016 6:26 AM
  • Teylyn,

    Thanks for your response. I have in fact done as exactly as you have described and found that after formatting the date on the pivot TABLE the pivot CHART did not reflect the change. Which is why I found my way here. Since none of the approaches I have taken have resulted in any modification to the date format on the chart. Is there some other action needed to force the chart to reflect the format change in the pivot?

    RitaMAC

    Monday, February 15, 2016 2:55 PM
  • I am experiencing the exact same issue with Excel 2016 (previously I used this solution successfully in Excel 2010). Regardless of how the date field in the pivot table is formatted (currently as 01-MAR-17) the axis label is always shown as MAR-17. I have been unable to find a solution to this issue.

    This occurs even when I format the pivot table before creating the chart.

    Chuck

    UPDATE:

    The key to success is to format the field using the field settings, not by formatting the table itself as in previous versions of Excel.

    Thursday, January 26, 2017 3:56 PM
  • That's what I said in my post above. Field Settings. 


    cheers, teylyn

    Thursday, January 26, 2017 8:48 PM
  • I just had this same issue and tried all the documented processes to fix it, such as removing blank dates, right clicking various properties, but no luck. In the issue turned out to be Regional settings.

    The Spread sheet was created by someone using a European locale with dd/mm/yy default format. My machine is mm/dd/yy. The solution was to delete the spread sheet. Switch my locale to English (British) retrieve the spread sheet and open, now the dates were able to be formatted.

    Dynamics NAV MVP

    Monday, September 18, 2017 2:48 PM
  • UPDATE:

    The key to success is to format the field using the field settings, not by formatting the table itself as in previous versions of Excel.

    I struggled with this for a couple of hours before realizing that if the data source has blanks for any of the category axis values, then the pivot table/chart will fail to show the Number Format button on the field settings dialog. That applies even if you've filtered out all the blank values. So I had to create a new data source with the blank-category rows deleted to get it to work.
    Wednesday, April 4, 2018 4:34 PM