Changing the date format in Pivot table (Power Pivot)

%3CLINGO-SUB%20id%3D%22lingo-sub-3202296%22%20slang%3D%22en-US%22%3EChanging%20the%20date%20format%20in%20Pivot%20table%20(Power%20Pivot)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3202296%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20trouble%20changing%20the%20date%20format%20in%20the%20pivot%20table%20loaded%20from%20Power%20Pivot.%20For%20normal%20pivot%20table%2C%20I%20can%20change%20the%20format%20in%20%22Field%20setting%22%20-%20Number%20Format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Number%20Format%20option%20is%20not%20available%20in%20my%20pivot%20table%2C%20so%20I%20changed%20the%20date%20format%20in%20Power%20Pivot%2C%20but%20the%20changed%20date%20format%20is%20not%20reflected%20in%20the%20pivot%20table.%20However%2C%20when%20trying%20to%20filter%20the%20table%2C%20I%20noticed%20the%20changed%20date%20format%20is%20showing%2C%20but%20just%20not%20in%20the%20Pivot%20table%2C%20which%20not%20showing%20the%20required%20format%20in%20the%20chart.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20what%20is%20the%20correct%20way%20to%20change%20the%20date%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TomH001_0-1645591466644.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350592i2018455AF84126A1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22TomH001_0-1645591466644.png%22%20alt%3D%22TomH001_0-1645591466644.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3202296%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3207688%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20the%20date%20format%20in%20Pivot%20table%20(Power%20Pivot)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3207688%22%20slang%3D%22en-US%22%3E%3CP%3EI%20found%20a%20solution%20to%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENeed%20to%20create%20two%20additional%20columns%20in%20Power%20Pivot%20using%3A%3C%2FP%3E%3CUL%3E%3CLI%3EMonthYear%20%3D%20Format(Date%2C%20%22MMM-YY%22)%26nbsp%3B%3C%2FLI%3E%3CLI%3EYearMonthNumber%20%3D%20Format(Date%2C%20%22YYYYMM%22)%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESort%20on%20the%20MonthYear%20column%20with%20YearMonthNumber%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am having trouble changing the date format in the pivot table loaded from Power Pivot. For normal pivot table, I can change the format in "Field setting" - Number Format.

 

The Number Format option is not available in my pivot table, so I changed the date format in Power Pivot, but the changed date format is not reflected in the pivot table. However, when trying to filter the table, I noticed the changed date format is showing, but just not in the Pivot table, which not showing the required format in the chart.

 

Not sure what is the correct way to change the date format.

 

 

TomH001_0-1645591466644.png

 

 

1 Reply

I found a solution to my problem.

 

Need to create two additional columns in Power Pivot using:

  • MonthYear = Format(Date, "MMM-YY") 
  • YearMonthNumber = Format(Date, "YYYYMM")

 

Sort on the MonthYear column with YearMonthNumber