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
ahmad ali
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

9 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

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!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies