Date formating in a pivot table

Copper Contributor

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 

8 Replies

@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. 

@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".

 

Screenshot 2020-05-13 at 08.11.56.png

Thank you. I will play with that,@AshaKantaSharma 

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-Dec

@LisaM295 

if 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.

@Sergei Baklan What is theis regarding? I have no idea what we are talking about so please provide some insight.

No such option for mac-book version (((
Real headache.
Thanks for this workaround.
But it is a bit funny way to work with formats in expensive product