Data Import & Export

How to Fix Excel Sorting Dates by Day Instead of the Full Date

Nimra Malik Oct 16, 2026 870 views

Question details

The user needs to sort imported dates chronologically, but Excel is sorting them by the specific day of the month rather than the full date.

How to Fix Excel Sorting Dates by Day Instead of the Full Date

Product: Excel
Device & OS: not provided
Scenario: Sorting imported data or values in a shared workbook where dates are unexpectedly treated as text.
Observed behavior: Excel sorts the date column based on the day portion (e.g., 01, 02) instead of the full chronological date because the system is interpreting the imported date values as text strings.

Before you start

Verify whether your dates are formatted as text by widening the column; standard text aligns to the left side of the cell, whereas proper Excel dates automatically align to the right.

Solution 1Recommended

Convert Text Dates to Real Dates Using Text to Columns

Use the Text to Columns wizard to force Excel to recognize imported text strings as actual chronological dates.

When data is imported from external sources, Excel may fail to recognize it as a date, defaulting it to a text format. This is often due to formatting differences or invisible characters. The Text to Columns feature quickly parses these text strings and translates them into the proper date serial numbers based on your chosen formatting, enabling standard chronological sorting.

Select the Date Column

Click the column letter header or highlight all the specific cells containing the dates that are sorting incorrectly.

Open the Text to Columns Wizard

Navigate to the 'Data' tab on the top ribbon and click on the 'Text to Columns' button located in the Data Tools group.

Skip to the Final Step

Ensure 'Delimited' is selected in the first step of the wizard, then click 'Next' twice to proceed directly to step 3.

Apply the Correct Date Format

Under the 'Column data format' section, select the 'Date' option. Choose the format from the drop-down that matches how your text currently looks (e.g., 'DMY' for Day-Month-Year), and click 'Finish'.

Convert Text Dates to Real Dates Using Text to Columns

Ready to Sort Chronologically: Once the conversion is complete, the dates will align to the right. You can now apply the sort function again, and Excel will correctly order the dates chronologically.

Organize Data Seamlessly

Fix Sorting Issues and Manage Dates Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive data tools, including Text to Columns, allowing you to quickly convert and sort problematic date formats. It offers a familiar interface, ensuring you can fix data import errors in seconds without advanced technical knowledge.

1. Highlight the Problematic Dates: Open your workbook in WPS Spreadsheet and select the column containing the dates that fail to sort chronologically.
2. Access the Data Tab: Navigate to the 'Data' tab located on the main ribbon and select 'Text to Columns'.
3. Configure the Wizard: Choose the 'Delimited' option, click 'Next' twice, and set the column data format to 'Date'.
4. Select Format and Finish: Pick the specific date format that matches your imported data (such as DMY) from the drop-down menu and click 'Finish'.

Highly compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Intuitive Text to Columns feature to quickly resolve text-formatted dates.Free, lightweight, and fast-loading spreadsheet application.Advanced data sorting and filtering without complex configurations.

Frequently Asked Questions

Why does Excel treat my imported dates as text?

Imported data often carries invisible formatting characters, or it uses a date structure (like DD/MM/YYYY) that conflicts with your computer's default regional settings, causing Excel to read them as standard text strings instead of date values.

Why does this sorting issue happen on my computer but not my colleague's?

This usually happens because computers can have different regional date settings configured in their operating systems. If your colleague's system matches the imported date format but yours does not, Excel will correctly read theirs as dates and yours as text.

How do I quickly check if dates are formatted as text without clicking on them?

Expand the column width to see the alignment. By default, Excel right-aligns real dates (which are stored as numbers) and left-aligns text strings. If your dates are hugging the left side of the cell, Excel sees them as text.

Excel & Data Problems