How to Sort Mixed Hours and Days Ranges Correctly in WPS Office Spreadsheets
Question details
The user needs to sort a text column containing both hour ranges and day ranges by true duration order rather than alphabetical text order.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet has duration ranges such as "4-8 hours" and "1-2 days", and the user needs to arrange them from shortest to longest.
- Observed behavior
- The desired state is a numeric sort in which all hour ranges appear before day ranges and ranges within each unit are ordered by their converted values.
Open the worksheet in WPS Office Spreadsheets and check that each range uses a consistent pattern, such as a number, a hyphen, a number, a space, and a unit label in hours or days.
Create a numeric helper column and sort the ranges by that column
Use this method when you need a reliable, repeatable way to sort mixed hour and day ranges without manually checking each row.
Excel and WPS Spreadsheets treat values such as "4-8 hours" and "1-2 days" as text, so the default A-to-Z sort does not follow true duration order. A helper column converts each range to a common unit, making the sort numeric and consistent.
Select the cell to the right of the first duration range and type a heading such as Helper_Hours.
In the first data cell of the helper column, enter =LEFT(A2,FIND("-",A2)-1)*IF(ISNUMBER(SEARCH("day",A2)),24,1). This extracts the lower bound and multiplies day values by 24 to convert them to hours.
Select the formula cell, then drag the fill handle down through all rows or double-click the fill handle to apply the formula to the entire list.
Select the original duration column and the helper column, go to the Data tab, click Sort, choose Helper_Hours in the Sort by list, set the order to Smallest to Largest, and click OK.
Sort Mixed Hours and Days Ranges in WPS Office Spreadsheets
Use a helper column in WPS Office Spreadsheets to convert day ranges to hours, then run a numeric Data sort so all rows appear in true duration order.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the workbook that contains the mixed hour and day ranges.
- 2. Add a helper column: In the cell to the right of the first range, enter =LEFT(A2,FIND("-",A2)-1)*IF(ISNUMBER(SEARCH("day",A2)),24,1), then fill the formula down through the list.
- 3. Sort by the helper column: Select the original duration column and the helper column, click the Data tab, choose Sort, set Sort by to the helper column, select Smallest to Largest, and click OK.
- 4. Verify the sorted order: Check that hour ranges appear before day ranges and that the numeric amounts increase row by row.
- 5. Save the sorted workbook: Click File > Save As to save the sorted workbook, or choose an .xlsx file to keep it compatible with Microsoft Excel.
Frequently Asked Questions
Why does Excel sort 1-2 days before 10-12 hours when I sort A to Z?
Because the values are text, not numbers. Excel compares characters in sequence, and "1-" comes before "10" in a text sort. Converting both ranges to hours in a helper column makes the sort numeric.
Can I sort by the upper bound of each range instead of the lower bound?
Yes. Instead of extracting the number before the hyphen, use a formula that returns the number after the hyphen and multiply by 24 when the cell contains "day". Then sort by that helper column.
What if my unit labels are shortened to "hrs" and "d" instead of "hours" and "days"?
Adjust the formula so the day condition checks for the actual day label used in your data, such as SEARCH("d",A2), and multiply by 24 only when that condition is true. The hour condition can remain 1.
Will this helper-column method work in both WPS Office Spreadsheets and Microsoft Excel?
Yes. The formula and Data > Sort steps work in both applications because they support the same functions and file formats, so you can open the sorted workbook in either program.
Do I need to keep the helper column after sorting?
No. The helper column only controls the sort order. After the rows are in the correct order, you can hide the column or delete it if you no longer need it.
