How to Fix Excel PivotTable Dates Sorting as Text
Question details
Users experience an issue where dates inside a PivotTable sort alphabetically rather than from oldest to newest.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Sorting a date field within a PivotTable to analyze chronological data.
- Observed behavior
- The date field sorts alphabetically because the source values, although looking like dates, are actually stored as text strings rather than numeric date serial values.
Check your original source data column to see if the dates align to the left side of the cell, which is a strong indicator that Excel is treating them as text.
Convert Text to Dates Using Text to Columns
This is the most efficient method to convert an existing column of text dates into real Excel date serial values directly in the source data.
Simply changing the cell format to 'Date' from the ribbon will not change the underlying data type. You must force Excel to re-evaluate the text as actual numeric dates using the Text to Columns wizard.
Navigate to your original source data and highlight the entire column that contains the text dates.
Go to the Data tab on the Excel ribbon and click on 'Text to Columns'.
In the Convert Text to Columns Wizard, keep 'Delimited' selected and click 'Next' twice until you reach Step 3.
Select the 'Date' radio button under Column data format. Use the dropdown to choose the format that matches how your text dates currently look (e.g., MDY for Month-Day-Year), then click 'Finish'.
Go back to your PivotTable, right-click inside it, and select 'Refresh'. Click the filter/sort dropdown on the date field and choose 'Sort Oldest to Newest'.

Use the DATEVALUE Function to Create a New Column
Ideal if you need to keep your original text column intact while generating a new column strictly for accurate chronological sorting.
Easily Manage and Sort PivotTable Data with WPS Spreadsheet
WPS Spreadsheet provides intuitive tools to instantly convert text strings into proper dates and build powerful PivotTables. Enjoy a lightweight interface with seamless data processing tools.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open your workbook containing the text-formatted dates.
- 2. Convert text to dates: Select the date column, click the Data tab, and choose 'Text to Columns' to convert the text strings into valid date values.
- 3. Create or Refresh PivotTable: Go to the Insert tab and click 'PivotTable'. If you already have one, right-click it and choose 'Refresh'.
- 4. Sort Chronologically: Add the corrected Date field to the Rows area, click the dropdown arrow next to the row label, and select 'Sort Oldest to Newest'.

Frequently Asked Questions
Why do my dates align to the left in Excel?
By default, Excel aligns text strings to the left and numeric values (which include real dates) to the right. If your dates are left-aligned without custom formatting applied, Excel is treating them as text.
Can I just change the cell format to 'Date' to fix the sorting?
No. Simply changing the format from the Home ribbon dropdown does not alter the underlying data type from text to a numeric serial value. You must use a tool like Text to Columns, Power Query, or a formula to force the conversion.
Why didn't my PivotTable update after I fixed the source data?
PivotTables cache their data and do not update automatically when source data changes. You must right-click anywhere inside the PivotTable and select 'Refresh' to pull in the corrected date values.
Can Power Query help fix text dates automatically?
Yes. If you import your data using Power Query, you can change the column's data type to 'Date' within the Power Query Editor. This ensures that the dates will always be recognized as chronological values when loaded into your PivotTable.




