Data Import & Export

How to Sort Mixed Excel Date Values Correctly

Muhammad Talha Oct 1, 2026 868 views

Question details

The user needs to sort dates exported from web reports that are a mixture of valid Excel dates and text values.

How to Sort Mixed Excel Date Values Correctly

Product: Spreadsheet
Device & OS: not provided
Scenario: Sorting data exported from web reports where dates are improperly formatted and inconsistent.
Observed behavior: Sorting fails to order dates chronologically, and attempting to convert them results in DATEVALUE errors due to mixed text and date formats.

Before you start

Select your column of mixed dates and expand the column width to clearly see how the dates are aligned; by default, valid dates align to the right, while text dates align to the left.

Solution 1Recommended

Convert Mixed Dates using Text to Columns

Use the Text to Columns feature to quickly force the spreadsheet to recognize mixed text and date formats as uniform valid date values.

This is the most efficient method for cleaning up exported data without needing to write complex formulas. It overrides text formatting and standardizes the entire column.

Select the Data

Highlight the entire column containing the mixed text and date values.

Open Text to Columns

Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.

Navigate the Wizard

Choose 'Delimited' in the first step and click 'Next' twice to reach Step 3 of the wizard.

Set Date Format

Select the 'Date' radio button, choose the correct format (e.g., MDY or DMY) that matches the text layout of your data, and click 'Finish'.

Sort the Cleaned Data

Select the newly converted column, go to the 'Home' tab to apply a uniform 'Short Date' format, and use the 'Sort' function to arrange the data chronologically.

Pro Tip: This method is highly effective for fixing structural data inconsistencies immediately after importing CSVs or web reports.

Solution 2

Use Formulas to Standardize Date Formats

Create a helper column using functions to convert text strings to date serial numbers safely while preserving existing valid dates.

Efficient Data Management

Sort and Convert Complex Data Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, including an advanced Text to Columns wizard and robust formula calculation capabilities, to effortlessly fix mixed date values and DATEVALUE errors.

1. Open Your File: Open your exported web report or dataset in WPS Spreadsheet.
2. Select the Column: Highlight the column that contains the mixed date formats.
3. Use Text to Columns: Navigate to the 'Data' tab, select 'Text to Columns', and follow the prompt to set the data format to 'Date'.
4. Sort the Data: Click 'Sort' under the 'Data' tab to arrange your freshly standardized dates chronologically.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Intuitive Text to Columns wizard for quick data conversion.Comprehensive formula support for advanced data manipulation.Free, lightweight, and easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do some exported dates appear as text?

Web systems and databases often export data in plain text formats to preserve visual formatting. If the spreadsheet application doesn't recognize the specific text pattern as a local system date format, it leaves the cell formatted as a text string instead of converting it to a date serial number.

How can I easily spot text dates in a column?

Without manually checking cell formats or formulas, you can widen the column. By default, spreadsheet applications align text strings to the left side of the cell and numerical values (which includes valid dates) to the right side.

Why am I getting a DATEVALUE error when converting dates?

The DATEVALUE function expects a text string that looks like a date. If you target a cell that already contains a valid numeric date, or if the text format is completely unrecognizable, it will return a #VALUE! error. Using the IFERROR function helps bypass this issue.

Excel & Data Problems