WPS Spreadsheet: Sort Mixed Excel Dates Chronologically
Question details
The user needs to sort a table chronologically when date entries use different formats, including full dates and four-digit years.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains day/month/year date values alongside entries that contain only a four-digit year, and the user wants one chronological sort order.
- Observed behavior
- Mixed date formats cannot be reliably sorted as entered because four-digit years and complete dates may be interpreted differently.
Check whether the full dates are stored as recognized date values and whether year-only entries contain exactly four digits. Confirm the data range, such as A2:A7, before entering the sorting formula.
Use SORTBY with a conversion rule for four-digit years
Use this dynamic-array formula when you want to sort the original entries while treating each year-only value as January 1 of that year.
The formula creates a chronological sort key for each entry. Values with four characters are converted to January 1 of that year, while complete date values are used as their existing dates.
Open the worksheet in WPS Spreadsheet and click an empty cell where the sorted results should begin, such as C2.
Enter =SORTBY(A2:A7,IF(LEN(A2:A7)=4,DATE(A2:A7,1,1),A2:A7)) and press Enter. Replace A2:A7 with the actual range containing your mixed date entries.
Check the spilled results below the formula cell. Four-digit years appear in chronological order as January 1 of the corresponding year, followed by or alongside full dates according to their actual date values.
Create a helper date key and sort the table
Use a helper column when you need a visible sort key or when your WPS Spreadsheet version does not support dynamic-array SORTBY formulas.
Sort Mixed-Date Excel Tables in WPS Spreadsheet
WPS Spreadsheet can sort mixed date data by generating a consistent chronological key for full dates and four-digit years. Use SORTBY for a formula-based result or a helper column for broader compatibility, then save the workbook in a familiar Excel format.
- 1. Open the workbook: Launch WPS Spreadsheet and open the Excel workbook containing the mixed date entries.
- 2. Select an output cell: Click an empty cell beside the source data where the sorted results can spill without overwriting existing content.
- 3. Apply the chronological formula: Enter =SORTBY(A2:A7,IF(LEN(A2:A7)=4,DATE(A2:A7,1,1),A2:A7)), adjusting A2:A7 to match your data range.
- 4. Verify and save the results: Review the sorted dates, then use File > Save As to save the workbook as an Excel Workbook (*.xlsx) when required.
Frequently Asked Questions
How does the formula handle a cell containing only a year?
IF(LEN(A2:A7)=4,DATE(A2:A7,1,1),A2:A7) converts a four-digit year into January 1 of that year, creating a sortable date value.
Can I sort the entire table instead of only the date column?
Yes. Use a helper Sort Key column, select the full table, open Data > Sort, and sort by Sort Key so the related row data stays together.
Why are some full dates still sorting incorrectly?
Those entries may be stored as text or may use an unrecognized date format. Convert them to valid date values first, or use a consistent regional date format before applying the sort.
What should I do if SORTBY is unavailable?
Create a helper column with =IF(LEN(A2)=4,DATE(VALUE(A2),1,1),A2), fill it down, and sort the full table by that helper column from oldest to newest.
