Forum Discussion
Date Format: Pivot Table vs Source Data
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
12 Replies
To change the date format in a pivot table in Office 365 for Enterprise on Windows 10 Pro, you can follow these steps:
- Click anywhere on the pivot table to select it.
- 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.
- Right-click on the date field and select "Value Field Settings" from the context menu.
- In the "Value Field Settings" dialog box, click on the "Number Format" button.
- In the "Format Cells" dialog box, select "Custom" from the category list on the left.
- 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.
- Click "OK" to apply the changes and close the "Format Cells" dialog box.
- 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.
