Forum Discussion

RDALE2435's avatar
RDALE2435
Copper Contributor
May 12, 2020

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, n...
Formulas and Functions
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 13, 2020

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

 

LisaM295's avatar
LisaM295
Copper Contributor
Jul 14, 2021
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
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 14, 2021

    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.

    • RDALE2435's avatar
      RDALE2435
      Copper Contributor
      Jul 18, 2021

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

Resources