WPS Spreadsheet: Convert Text Dates to Real Dates for Correct Sorting
Question details
The user needs to convert dates imported as text into real date values so they sort chronologically instead of alphabetically.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A bank report contains dates such as 1/27/2023 and 1/3/2023 in a spreadsheet column, and the user wants to sort them by date.
- Observed behavior
- The date values are treated as text, so sorting places them in alphabetical rather than chronological order.
Confirm that the selected column contains consistently formatted month/day/year values, such as 1/27/2023. Select the complete date column, including all imported values, before opening the conversion tool.
Convert text dates with Text to Columns in WPS Spreadsheet
Use this method when imported dates follow a consistent format such as month/day/year and are sorting as text.
Text to Columns can reinterpret the selected text as date serial values without splitting the date into separate columns. After conversion, apply a date format and sort the column again.
In WPS Spreadsheet, click the column letter or select the range containing the imported text dates.
Go to the Data tab and click Text to Columns.
In the conversion wizard, select Delimited and click Next. Clear every delimiter checkbox, then click Next again.
In the Column data format section, select Date and choose MDY from the format dropdown, then click Finish.
Select the converted column, open the Home tab's number format controls, choose a date format such as m/d/yyyy, and use Data > Sort to sort the values chronologically.
Convert imported text dates and sort them correctly in WPS Spreadsheet
WPS Spreadsheet provides a familiar Text to Columns workflow for converting bank-report date strings into real date values. You can set the source order to MDY, apply a readable date format, and sort the results chronologically.
- 1. Select the imported dates: Open the worksheet in WPS Spreadsheet and select the column containing the text dates.
- 2. Start the conversion: Choose Data > Text to Columns, select Delimited, and click Next.
- 3. Preserve each date: Clear all delimiter options, click Next, choose Date and MDY, then click Finish.
- 4. Apply a date format: Use the Home tab's number format controls to apply m/d/yyyy or another suitable date format.
- 5. Sort chronologically: Return to the Data tab and sort the converted date column from oldest to newest or newest to oldest.
Frequently Asked Questions
Why do imported dates sort alphabetically in WPS Spreadsheet?
The values are stored as text instead of date serial numbers. Convert the column through Data > Text to Columns and choose the matching date order, such as MDY.
Which date format should I select for values like 1/27/2023?
Select MDY because the value uses month/day/year order. If your source uses another order, choose the matching format to prevent incorrect conversions.
Do I need to select a delimiter in Text to Columns?
No. Choose Delimited, clear all delimiter checkboxes, and continue. This keeps each complete date in one cell while allowing WPS Spreadsheet to interpret it as a date.
How can I tell whether the conversion worked?
Apply a date format such as m/d/yyyy and sort the column. Values should sort by calendar order, with January 3 before January 27, rather than by the first characters of the text.
What should I do if some dates remain incorrect after conversion?
Check whether those cells use a different date order, contain extra spaces, or include invalid dates. Clean the inconsistent entries, select the full column, and run Data > Text to Columns again with the correct date format.
