How to Change Date Format in Pivot Table in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Change Date Format in Pivot Table in Excel: 4 Methods

Let’s introduce today’s dataset where Sales of some Product Categories is provided along with Order Date and corresponding States.

Dataset

However, creating a Pivot Table is a simple task. Also, check the circle before the New Worksheet and the box before the Add this data to the Data Model option.

Method

And move the Order Date into the Rows area and Sales into the Values area.

Method

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.

Method

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

Excel Change Date Format in Pivot Table Using the Format Cells

Eventually, the dates will be changed and stored in your desired format as shown in the following screenshot.

Excel Change Date Format in Pivot Table Using the Format Cells

Note: Using this simple method will be handy if you add your source data into the Data Model.


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.

Excel Change Date Format in a Pivot Table’s 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.

Excel Change Date Format in a Pivot Table’s Chart

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.

Excel Change Date Format in a Pivot Table’s Chart

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.

Pivot Table without Data Model

Now, if you click on the Field Settings, you’ll definitely get the Number Format option. And click on the option.

Excel Change Date Format in a Pivot Table’s Chart

Therefore, you’ll get the as usual features of the Format Cells option. Now, just choose your desired format.

Excel Change Date Format in a Pivot Table’s Chart

After pressing OK, you’ll get the Pivot Chart where the dates in the horizontal axis are existing in your expected format.

Excel Change Date Format in a Pivot Table’s Chart

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


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

Grouping Dates

➤ Secondly, you’ll get the following dialog box namely Grouping. And choose Years from the options.

Grouping Dates

Finally, you’ll get the sum of sales based on the years instead of the dates.

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

Grouping Dates

The output will look like the following.

Grouping Dates

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.

Grouping Dates


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.

Excel Change Date Format in Pivot Table Timeline Slicer

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.

Excel Change Date Format in Pivot Table Timeline Slicer

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.


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

Excel Change Date Format in Pivot Table Ungrouping Dates and Adjust Using Excel Function

Now, you have to refresh the Pivot Table. And you need to click on the Refresh option in the PivotTable Analyze tab exactly.

Excel Change Date Format in Pivot Table Ungrouping Dates and Adjust Using Excel Function

Then, you need to remove the previous Order Date field. Rather, you have to add the Formatted Dates field in the Rows area.

Excel Change Date Format in Pivot Table Ungrouping Dates and Adjust Using Excel Function

Shortly, you’ll get your desired date format.

Excel Change Date Format in Pivot Table Ungrouping Dates and Adjust Using Excel Function

Similarly, you may change the date format into any valid format using the Excel function.

Read More: Remove Time from Date in Pivot Table in Excel


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.


Download Practice Workbook


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.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo