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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
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?
 

Attachments

Joined
Oct 15, 2013
Messages
503
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

Thread Starter
Joined
Mar 11, 2010
Messages
212
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

Thread Starter
Joined
Mar 11, 2010
Messages
212
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?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top