Forum Discussion

Brass Contributor

Mar 06, 2023

Solved

I have problems with the dates in Pivot Table

This is my data. I wanted my date to be ascending orders which it follows by month, days and years. but now, it only follows the days only. How should I do here? Thank you. Saza

excel

Riny_van_Eekelen
Mar 06, 2023
syazaaoa95 Your 'dates' are in fact texts that look like dates and the pivot table merely sorts them in alphabetical order (0 - 9, a -z ). You need to transform them into real dates.

There are several way to fix that, depending on where the data comes from. If import from e.g. a CSV or TXT file, use Power Query the legacy import From Text to import the data and set the date format correct from the beginning. If the 'dates' are in an Excel sheet from the start use text-to-columns (on the Data ribbon) or the DATEVALUE function. The latter will transform the date text into a number which you can then format as a Date.

So, please tell a little bit more on the background of these 'dates' or experiment on your own with any of the above-mentioned options.

Riny_van_Eekelen

Platinum Contributor

Mar 06, 2023

syazaaoa95 Your 'dates' are in fact texts that look like dates and the pivot table merely sorts them in alphabetical order (0 - 9, a -z ). You need to transform them into real dates.

There are several way to fix that, depending on where the data comes from. If import from e.g. a CSV or TXT file, use Power Query the legacy import From Text to import the data and set the date format correct from the beginning. If the 'dates' are in an Excel sheet from the start use text-to-columns (on the Data ribbon) or the DATEVALUE function. The latter will transform the date text into a number which you can then format as a Date.

So, please tell a little bit more on the background of these 'dates' or experiment on your own with any of the above-mentioned options.

syazaaoa95

Brass Contributor

Mar 06, 2023

Thank you for responding.
My problem is in pivot table format.
When I did not put the dates in Pivot Table, the arrangement on the dates are in order.
But when I insert into Pivot Table, the problems become the one I attached.

For your suggestion, can I use it? Because it is in the form of PivotTable now.