SOLVED

changing Date format in a pivot table

Contributor

Hello,

 

Could you please help me .... I can't change the date format in the pivot chart, I want it to like:                6-Jan-2018    ....please find the file attached   Thanks

 

Capture.PNG

20 Replies
Normally the chart should follow the date format as set in the source data.

thanks,

 

As you see the source data is a pivot table on the left of the pivot_chart

 

I ungrouped the tabled but didn't work!

I see, I can't convince Excel to display the dates differently either. Probably some silly limitation of a pivot chart. Anything I set for the chart axes number format or any date formatting I apply to the pivottable or its source data is completely ignored. Sounds like a bug to me.
In retrospect:
- rightclick the pivottable field with the date, select "Field settings". Click the Number format button and change the display format to the date format you like.

thanks,

 

I tried ... it's not working 

I've attached the file in case you want to have a look on it

best response confirmed by ahmad ali (Contributor)
Solution

In the attached file, all I did was:

- Right-click cell A4 and choose "Field Settings"

- Click the Number Format button

- Choose Date category and select "March 14, 2012"

- OK your way out.

Thank you so much ... it's a bit Embarrassing for me .... I don't know why it wasn't working for me

Thank you so much ... it's a bit Embarrassing for me .... I don't know why it wasn't working for me
Don't be embarrassed, Excel can be confusing!

@Jan Karel PieterseI have a pivot table and chart in (current) Office 365 with dates in the row column; when I follow the same steps as described below, there is no "Number Format" button showing in the Field Settings dialog - see screen copy below.  Why is that?
I managed to change the date format within the pivot table (using "ungroup"), but this new format does not get reflected in the Pivot Chart.  How can I solve this?

clipboard_image_0.png

 

@iveccc as far as I am aware of there is no Number Format option for fields in the Row or Filter section. You can only set the formats for the fields in the Column or Values section. 

 

Have you tried to re-organize your data in the way to have Dates field in Column section?

Also it would be good if you can attach your file - to check on you example.

 

Cheers,

Branka

@iveccc 

It depends on do you add data to data model creating PivotTable or not:

image.png

@Sergei Baklan Can you explain more how do you do that?

Thanks

@kenteo 

Sorry, I didn't catch what is the question about. How to create PivotTable with and without loading data to data model?

Hi, Yes. Would like understand more about these two differences.

 

By default if we create from table to pivottable, it will using non-data model?

 

I also notice that the date format can change if you set to ungroup the field. By default it is group it, then even you change the number format for change date format, it will not work

@kenteo 

Oops, I don't remember that is default option for newly installed Excel. In general that is one which set here

image.png

 

@Jan Karel Pieterse 

I was having the same problem with dates, Ungrouping them worked

@Sergei Baklan 

I am using desktop version Excel for Mac.

I can not find any way to change date format. There is no option for that neither in PivotTable field settings nor in excel option.

SOS

@AndreyKlepikov 

Sorry, I don't use Mac. @Riny_van_Eekelen , if you know.