Pivot table date formatting

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
528
I have a workbook with several pivot tables created from the same pivot cache. The dates in the original data table are formatted as mm/dd/yyyy (11/30/2018). All of the pivot tables, however, show the date as dd-mmm (30-Nov). I have gone into Field Settings and changed the number format to mm/dd/yyyy but it still displays dd-mmm.

In a month we will start another year's data and I'm fairly certain management will want to see 2019 somewhere in the date. How can I format the date so it displays properly?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Noramlly I just use the Group function of the Pivot Table for Dates.
Right-Click on a Date, and then choose the Group By. Then Choose the suitable grouping levels.
Management may not care about specific days, and you may omit that selection or just collapse the rows to Months
 
Upvote 0
Most of the time I group the dates, too, but in this instance, specific dates are important. I just thought it was odd that the date format changed and would not allow me to adjust the format.
 
Upvote 0
It is odd. I guess MS figured that was duplication "no-no"
You can swap the "Month" for Year so previous Years for that month are easy to see.

However, to get the formatting done the way you want, you'll need to duplicate that date field.
 
Upvote 0
I never thought of duplicating the field. That shouldn't be too difficult to incorporate into the data.

Thank you
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top