Date Format: Pivot Table vs Source Data

[Ross321]

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

[#2]

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

[#3]

@Rodrigo_ 

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

[#4]

@Ross321 

That's here

and next

[#5]

Got it, thank you.

[#6]

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

[#7]

@office1460 

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

[#8]

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

Data Source Sorted:Pivot Table Sorted:

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.

[#9]

@OrinWhitener2787 

In PivotTable

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

[#10]

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

[#11]

@OrinWhitener2787 , you are welcome

[#12]

@office1460  hii is it working date formatting