# Excel Pivot Table Date is in wrong Format and I can't get it to change



## SlowHnds (Mar 11, 2010)

in my data source the date field is custom ddd mmm dd yyyy 

When I create the pivot table it shows as dd-mmm but when I try to format the field it already shows the format correctly but the sample show dd mmm

ie. Source table shows Thu Mar 26 2015
Pivot Table Shows 26-Mar

I am unable to reformat it to show the source.

So how do I get the pivot table to display the right date format?


----------



## CodeLexicon (Oct 15, 2013)

Have you definitely ensured all cells in that column are formatted the same in the source table?


I formatted the current date as you describe and created a pivot table with that data and Excel automatically used the same formatting. I'm using Excel 2013.


----------



## SlowHnds (Mar 11, 2010)

All cells are formatted to display the same.

I think I've found part of the issue in that I wanted to put date and time in

ie. ddd mmm dd yyyy h:m AM/PM and entered data for the change but it wouldn't total correctly without grouping and the grouping is what changes the date to dd-mmm and it can then not be formatted any differently. As soon as I took out the h:mm entries it shows up displayed correctly in pivot table but I want the date and time on the data table. So I'm sort of at a one or the other with neither being perfect.

So I do without the times and settle for the short date or do without the times but get Thu March 26, 2015 in the pivot table. The times do make the data more valuable as it can show trends.


----------



## SlowHnds (Mar 11, 2010)

Because I have multi-months and I want grouped by Month 1 day

The pivot table shows dd MMM ie.

26 Mar
27 Mar
28 Mar
29 Mar
30 Mar
31 Mar
1 Apr
2 Apr
3 Apr

Nothing I do in the pivot table changes the date format

It has something to do with the grouping because if I ungroup the date format shows up correctly as it is formated in the data table. ie 
Tue Mar 31 2015
Wed Apr 01 2015 etc

It is only when I group it that the date format drops to dd mmm ie 1 Apr

Any way I can change it so when it is grouped month day that it shows dates as 
ddd mmm dd yyyyy
Tue Mar 31 2015
Wed Apr 01 2015

This is after I removed all the times from the data and only enter mm/dd/yyyy and set the format to show ddd mmm dd yyyyy and it does display correctly. The pivot table without grouping shows correctly. As soon as I apply any grouping it only will display
d-mmm ie 1-Apr even though the format applied in the pivot table shows
ddd mmm dd yyyy.

So this must be a "thing" with Excel any work arounds?


----------

