Date Format: Pivot Table vs Source Data

Copper Contributor

I formatted the date column in the source data how I wanted it, which is month/ year (for example May-2015 or Jun-2022). 

When I created a pivot table from this data, it reformatted the date to mm/dd/yyyy (for example 4/7/2014). 

 

Question: how do I get the date format in the pivot table to match the source data?  Or alternatively, how can I change the date format in the pivot table? 

I need to shorten the date format, it's taking up too much room to display properly. 

 

Using Office 365 for Enterprise on Windows 10 Pro

 

thanks,

Ross

11 Replies

@Ross321 

To change the date format in a pivot table in Office 365 for Enterprise on Windows 10 Pro, you can follow these steps:

  1. Click anywhere on the pivot table to select it.
  2. In the "PivotTable Analyze" or "Design" tab of the ribbon, click on "Fields, Items, & Sets" and select "Fields" or "Value Fields" depending on the type of field you want to format.
  3. Right-click on the date field and select "Value Field Settings" from the context menu.
  4. In the "Value Field Settings" dialog box, click on the "Number Format" button.
  5. In the "Format Cells" dialog box, select "Custom" from the category list on the left.
  6. In the "Type" field on the right, enter the desired date format that matches your source data. For example, if your source data is formatted as "May-2015" or "Jun-2022", you can enter "mmm-yyyy" in the "Type" field to display the date in the same format in the pivot table.
  7. Click "OK" to apply the changes and close the "Format Cells" dialog box.
  8. Click "OK" again to close the "Value Field Settings" dialog box.

Your pivot table should now display the date field in the desired format that matches your source data.

To shorten the date format in the pivot table, you can simply modify the format string in step 6 above. For example, if you want to display only the month and year in the format "mmm-yy", you can enter "mmm-yy" in the "Type" field.

@Rr_ 

Thanks for the instructions.  When I get to step 2, there is no "fields" available to select in either the Pivot table or Pivot chart...

 

 

2023-03-28 12_10_36-03-21-2023 Analysis Sandbox(2).xlsx - Excel.jpg2023-03-28 12_15_52-03-21-2023 Analysis Sandbox(2).xlsx - Excel.png

@Ross321 

That's here

image.png

and next

image.png

 

Got it, thank you.

@SergeiBaklan what if you dont have that number format under the field settings 

office1460_0-1700074570535.png

 

 

@office1460 

Perhaps you have texts which looks like date, not dates which are actually (in behind) numbers.

@SergeiBaklan What if one of the things being sorted on is an aggregated group name abbreviation that comes out to something like "JAN" and the pivot table seems to be auto interpreting it as "January" despite all data types being set appropriately as Text and non-pivot tables with the same Data Type settings are sorting alphabetically appropriately?

 

example:

  1. Column Header "Group Name".
    1. All "Group Name" cells set as Text data type.
    2. In Pivot Table all "Group Name" column cells set as Text data type.
  2. Multiple rows with "Group Name" values of "JAN" and "AST".
  3. On source data sorting by "Group Name" sorts as:
    1. AST
    2. JAN
  4. On pivot table sorting by "Group Name" sorts as: 
    1. JAN
    2. AST

Some Screenshots below:

 

Data Source unsorted:

pivot_sort_behavior_1.png

Data Source Sorted:pivot_sort_behavior_2.pngPivot Table Sorted:

pivot_sort_behavior_3.png

Is something like this just an extremely native limitation of current Excel and its ability to respect Data Types when creating a pivot table with source data at the moment? Doesn't seem like there are many resources specifically about this sorting issue and seems like you can't edit the default 3 letter sort list for the months in Excel's advanced options.

@OrinWhitener2787 

In PivotTable

More Sort Options->More Options->disable Sort Automatically->choose No Calculations

image.png

Awesome, thank you for such a prompt reply. That was exactly what I needed.

@OrinWhitener2787 , you are welcome

@office1460  hii is it working date formatting