SOLVED
Home

changing Date format in a pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-359648%22%20slang%3D%22en-US%22%3Echanging%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359648%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%20....%20I%20can't%20change%20the%20date%20format%20in%20the%20pivot%20chart%2C%20I%20want%20it%20to%20like%3A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%206-Jan-2018%26nbsp%3B%20%26nbsp%3B%20....please%20find%20the%20file%20attached%26nbsp%3B%20%26nbsp%3BThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84966i6ED6701F66A0980A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-359648%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359876%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359876%22%20slang%3D%22en-US%22%3EDon't%20be%20embarrassed%2C%20Excel%20can%20be%20confusing!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359874%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359874%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20...%20it's%20a%20bit%20Embarrassing%20for%20me%20....%20I%20don't%20know%20why%20it%20wasn't%20working%20for%20me%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359873%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359873%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20...%20it's%20a%20bit%26nbsp%3BEmbarrassing%20for%20me%20....%20I%20don't%20know%20why%20it%20wasn't%20working%20for%20me%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359840%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359840%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20attached%20file%2C%20all%20I%20did%20was%3A%3C%2FP%3E%0A%3CP%3E-%20Right-click%20cell%20A4%20and%20choose%20%22Field%20Settings%22%3C%2FP%3E%0A%3CP%3E-%20Click%20the%20Number%20Format%20button%3C%2FP%3E%0A%3CP%3E-%20Choose%20Date%20category%20and%20select%20%22March%2014%2C%202012%22%3C%2FP%3E%0A%3CP%3E-%20OK%20your%20way%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359768%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359768%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20...%20it's%20not%20working%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20file%20in%20case%20you%20want%20to%20have%20a%20look%20on%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359763%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359763%22%20slang%3D%22en-US%22%3EIn%20retrospect%3A%3CBR%20%2F%3E-%20rightclick%20the%20pivottable%20field%20with%20the%20date%2C%20select%20%22Field%20settings%22.%20Click%20the%20Number%20format%20button%20and%20change%20the%20display%20format%20to%20the%20date%20format%20you%20like.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359757%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359757%22%20slang%3D%22en-US%22%3EI%20see%2C%20I%20can't%20convince%20Excel%20to%20display%20the%20dates%20differently%20either.%20Probably%20some%20silly%20limitation%20of%20a%20pivot%20chart.%20Anything%20I%20set%20for%20the%20chart%20axes%20number%20format%20or%20any%20date%20formatting%20I%20apply%20to%20the%20pivottable%20or%20its%20source%20data%20is%20completely%20ignored.%20Sounds%20like%20a%20bug%20to%20me.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359673%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359673%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20see%20the%20source%20data%20is%20a%20pivot%20table%20on%20the%20left%20of%20the%20pivot_chart%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20ungrouped%20the%20tabled%20but%20didn't%20work!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359671%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359671%22%20slang%3D%22en-US%22%3ENormally%20the%20chart%20should%20follow%20the%20date%20format%20as%20set%20in%20the%20source%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091216%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3EI%20have%20a%20pivot%20table%20and%20chart%20in%20(current)%20Office%20365%20with%20dates%20in%20the%20row%20column%3B%20when%20I%20follow%20the%20same%20steps%20as%20described%20below%2C%20there%20is%20no%20%22Number%20Format%22%20button%20showing%20in%20the%20Field%20Settings%20dialog%20-%20see%20screen%20copy%20below.%26nbsp%3B%20Why%20is%20that%3F%3CBR%20%2F%3EI%20managed%20to%20change%20the%20date%20format%20within%20the%20pivot%20table%20(using%20%22ungroup%22)%2C%20but%20this%20new%20format%20does%20not%20get%20reflected%20in%20the%20Pivot%20Chart.%26nbsp%3B%20How%20can%20I%20solve%20this%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163808i421D632F5E061DEF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091544%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F506743%22%20target%3D%22_blank%22%3E%40iveccc%3C%2FA%3E%26nbsp%3Bas%20far%20as%20I%20am%20aware%20of%20there%20is%20no%20Number%20Format%20option%20for%20fields%20in%20the%20Row%20or%20Filter%20section.%20You%20can%20only%20set%20the%20formats%20for%20the%20fields%20in%20the%20Column%20or%20Values%20section.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20you%20tried%20to%20re-organize%20your%20data%20in%20the%20way%20to%20have%20Dates%20field%20in%20Column%20section%3F%3C%2FP%3E%3CP%3EAlso%20it%20would%20be%20good%20if%20you%20can%20attach%20your%20file%20-%20to%20check%20on%20you%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3EBranka%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1091562%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20Date%20format%20in%20a%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1091562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F506743%22%20target%3D%22_blank%22%3E%40iveccc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20do%20you%20add%20data%20to%20data%20model%20creating%20PivotTable%20or%20not%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20714px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F163850i29E5B98B5ADA2A6E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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

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

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!

Highlighted
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.
Highlighted
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.
Highlighted

thanks,

 

I tried ... it's not working 

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

Highlighted
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.

Highlighted

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

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

@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

 

Highlighted

@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

Highlighted

@iveccc 

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

image.png