Forum Discussion
Date formating in a pivot table
I am having a challenge changing the date format in pivot tables. All my table seem to default to year, quarter, month; in lieu of the actual date. While there are times the prior format is useful, not always and to simply change the format does not seem to do anything because the unwanted format remains. Is there something that I am missing or is there some esoteric
RDALE2435 As an alternative, format the column that holds the dates in your pivot table to the date format of your desire. Then, make sure to check the box "Preserve cell formatting upon update" (as shown in the picture towards the bottom) under "PivotTable Options".
- No such option for mac-book version (((
Real headache. - The box is checked and my dates are still not showing correctly.
Date format of my date = Short Date - 12/31/2021
Pivot format - 31-Decif you UNCHECK "preserve cell formatting" PivotTable takes format assigned to the field. How to set format depends on did you add data to data model creating PivotTable or not.
If not, Field Settings->Number format->select proper date format or set it in Custom
If with data model, open it in Power Pivot and format date field from menu.
If "preserve cell format" checked it shall take format applied to the cells in the grid.
RDALE2435 You can create an additional column in dataset with formula =TEXT(DATE,"DD-MM-YYYY") and use this date format in your PT which will not create any additional dataset in your PT.
Hope this helps.
- Thanks for this workaround.
But it is a bit funny way to work with formats in expensive product Thank you. I will play with that,AshaKantaSharma
