Function Problems

Why Excel Does Not Sort Dates in Chronological Order and How to Fix It

Kushani Nimanthika Oct 1, 2026 869 views

Question details

The user needs to sort dates from oldest to newest, but the spreadsheet fails to sort them chronologically because the date values are recognized as text strings rather than valid date formats.

How to Fix Excel Not Sorting Dates in Chronological Order

Product: Microsoft Excel
Device & OS: not provided
Scenario: Sorting a dataset by a date column to organize records chronologically.
Observed behavior: Dates are sorted alphabetically or randomly instead of chronologically because Excel treats the date entries as text values rather than real serial numbers.

Before you start

Before converting data formats, save a backup copy of your worksheet to prevent any accidental data loss or shifting during the conversion process.

Solution 1Recommended

Convert Text to Valid Dates and Sort

Verify if the dates are formatted as text, convert them to genuine serial number dates, and apply the sort function to the entire data range.

Excel handles dates as serial numbers (e.g., January 1, 2023, is 44927). When dates are imported or entered incorrectly, they are stored as text. Text strings cannot be sorted chronologically by Excel's standard sort tool.

Test for text formatting

Select the cells containing your dates. Go to the Home tab and temporarily change the Number Format from 'Date' to 'General'. If the cells change to a 5-digit number (e.g., around 45000), they are genuine dates. If they still look like standard dates, they are stored as text.

Convert text to dates

If your dates are text, highlight the column. Navigate to the Data tab and click 'Text to Columns'. Choose 'Delimited', click Next twice, and on the third step, select 'Date' under Column data format. Choose the correct format (e.g., MDY) and click Finish.

Select the complete data range

Ensure you highlight your entire dataset, not just the date column. This prevents your data from becoming misaligned when sorting.

Sort from oldest to newest

Navigate to Data > Sort. In the Sort dialog box, select your Date column under 'Sort by', choose 'Cell Values' under 'Sort On', and select 'Oldest to Newest' under 'Order'. Click OK.

Data Alignment: Always select the entire table before sorting to ensure that the data in adjacent columns stays matched with the correct dates.

Efficient Date Sorting in WPS Office

Easily Manage and Sort Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data formatting and sorting tools that perfectly handle complex data tasks. Easily convert text formats to dates and organize your spreadsheets exactly how you need them.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the dates you want to sort.
2. Identify formatting: Highlight the date cells, right-click, and select 'Format Cells' to verify if they are set to Date or Text.
3. Convert if necessary: If stored as text, go to the 'Data' tab, click 'Text to Columns', and follow the wizard to output them as Date formats.
4. Apply sorting: Highlight the full data range, click 'Data' > 'Sort'. Select the date column as your primary key and choose 'Oldest to Newest'.

100% compatible with Microsoft Excel (.xlsx) file formatsIntuitive Text to Columns wizard for quick data conversionAdvanced custom sorting options for complex data setsFree, lightweight, and incredibly fast to launch

microsoft office alternative - wps office

Frequently Asked Questions

How do I know if my dates are stored as text?

Select the date cells and change the number format to 'General'. Genuine dates will display as a serial number (e.g., 44927). If the cell still displays a date format like '1/1/2023', it is stored as text.

Why are dates imported from a CSV not sorting properly?

CSV files are plain text files. When opened in a spreadsheet, dates may not match your system's regional date settings, causing them to remain as raw text. You need to use the 'Text to Columns' feature to parse them into proper date values.

Can I sort dates by month and ignore the year?

Yes. Create a new helper column next to your dates and use the formula =MONTH(A2) to extract the month number. You can then sort your entire dataset based on this new helper column.

Excel & Data Problems