Md. Abdul Kader
Pivot Table, one of the powerful features in Excel, analyzes the larger dataset efficiently. But probably you have faced the problem of date formatting in the Pivot Table. In this article, I’ll demonstrate 4 methods to change date format in Pivot Table in Excel with proper explanation.
Download Practice Workbook
4 Methods to Change Date Format in Pivot Table in Excel
Let’s introduce today’s dataset where Sales of some Product Categories is provided along with Order Date and corresponding States.
However, creating a Pivot Table is a simple task. Also, check the circle before the New Worksheet and box before the Add this data to the Data Model option.
And move the Order Date into the Rows area and Sales into the Values area.
Finally, you’ll get the following Pivot Table where the dates are automatically changed from the defined format in the source data. The date format in the source dataset is dd-mm-yyyy but in the case of the created Pivot Table it is mm-dd-yyyy.
Most likely, you have encountered this type of problem. Let’s explore the methods to solve the issue.
1. Using the Format Cells to Change the Date Format in Pivot Table
In the beginning method, I’ll show you the use of the widely Format Cells option.
To apply this feature, you need to select the entire cell range first. Then, press CTRL + 1 for opening the dialog box namely Format Cells. Next, move the cursor over the Date category under the Number tab. Finally, choose your desired date format (e.g. 14-Mar-2012).
Eventually, the dates will be changed and stored in your desired format as shown in the following screenshot.
Note: Using this simple method will be handy if you add your source data into the Data Model.
Read More: Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
2. Changing the Date Format in a Pivot Table’s Chart
In the second method, you’ll show the process of changing date format in the case of Pivot Chart. You can easily create a Pivot Chart in Excel. In the following example, I created a Pivot Chart from the previously generated Pivot Table. Again the date format is changed automatically in the horizontal axis of the chart.
For modifying the date format of the Pivot Chart, you have to click on the Order Date located at the lower-left corner of the chart. And then, you’ll see some options and choose the Field Settings option.
Immediately, the dialog box of the Field Settings will be opened. But unfortunately, you won’t see the Number Format option at the lower-right side of the dialog box. Because the Pivot Table is added to the Data Model.
However, to get the Number Format option for adjusting any type of formatting, you need to keep unchecked the box before the Add this data to the Data Model option.
Now, if you click on the Field Settings, you’ll definitely get the Number Format option. And click on the option.
Therefore, you’ll get the as usual features of the Format Cells option. Now, just choose your desired format.
After pressing OK, you’ll get the Pivot Chart where the dates in the horizontal axis are existing in your expected format.
Note: Here, I showed you the application of the Number Format option in a Pivot Chart as an example. Certainly, you can apply the option for changing the date format in your Pivot Table.
Related Content: How to Change Default Date Format from US to UK in Excel (3 Ways)
Similar Readings:
- How to Convert Text to Date with Excel VBA (5 Ways)
- Convert Date to Day of Week in Excel (8 Methods)
- How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Stop Excel from Auto Formatting Dates in CSV (3 Methods)
3. Grouping Dates and Changing the Date Format in Pivot Table
Initially, you may be surprised why I am talking about the grouping dates instead of formatting dates!
Literally, the grouping dates and formatting dates are not the same. But using this, you’ll get your desired output quickly if you want to change the dates into years, quarters, months, or something like that.
3.1. Group Dates by Month
Assuming that you want to obtain the summarized data based on years and not in the dates wise actually.
To execute the task, follow the sequential steps.
➤ Firstly, click on the Group Selection option in the PivotTable Analyze tab while keeping the cursor over a cell of the Order Date (Row Labels).
➤ Secondly, you’ll get the following dialog box namely Grouping. And choose Years from the options.
Finally, you’ll get the sum of sales based on the years instead of the dates.
3.2. Grouping Quarter and Month Combinedly
Furthermore, you may choose any other options from the Grouping dialog box. You can even get the data based on days. However, in the following example, I am showing the combined application of quarters and months.
The output will look like the following.
Moreover, if you click on the plus sign before the month name, you’ll get the dates. Then, you might use the Format Cells if you want to format the dates again.
3.3. Inserting Timeline Slicer
In addition, you may insert a Timeline Slicer instead of Grouping Dates from the PivotTable Analyze tab.
For inserting this you have to select a cell within the Order Date, and then, choose the Insert Timeline option from the Filter ribbon.
Actually, this type of timeline slicer provides an outstanding visual appearance as well as you can easily get the output based on all periods that you have.
In the above picture, I just click on the gray-colored shape under 2022, and the timeline slicer shows all the sum of sales belonging to the year within seconds.
Read More: Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
4. Ungrouping Dates and Adjust Using Excel Function
Lastly, if you want to use the Excel function to change the date format, you may accomplish the task easily.
For doing such thing, you have to add a helper column e.g. Formatted Dates where you may use the TEXT function and the formula for the C5 cell is-
=TEXT(B5,”dd/mm/yyyy”)
Here, B5 is the starting cell of the Order Date and dd/mm/yyyy is the date format.
Now, you have to refresh the Pivot Table. And you need to click on the Refresh option in the PivotTable Analyze tab exactly.
Then, you need to remove the previous Order Date field. Rather, you have to add the Formatted Dates field in the Rows area.
Shortly, you’ll get your desired date format.
Similarly, you may change the date format into any valid format using the Excel function.
Read More: How to Use Formula to Change Date Format in Excel (5 Methods)
Things to Remember
While modifying the date format, you should be careful about the Data Model. For example, you may check whether the default option of turning on the Data Model for each Pivot table is checked or not.
If you want to change the date format using the Number Format option from the Field Settings, make sure that you uncheck the box of the default option (yellow-colored in the following image).
Notably, you can easily go to the Excel Options by clicking File > Options.
Conclusion
That’s the end of today’s session. This is how you may change the date format in the Pivot Table in Excel. I strongly believe this article will articulate your Excel journey. Anyway, if you have any queries or recommendations, please share them in the comments section.
