Others

How to Sort Excel Times Starting at 9:00 PM

Maira Mehtab Sep 27, 2026 868 views

Question details

Create a custom sort order for 30-minute time intervals that begins at 9:00 PM and ends at 8:30 PM the following day.

Product: Excel
Device & OS: not provided
Scenario: Organizing scheduling or shift data where the start time is strictly 9:00 PM rather than the standard midnight start time.
Observed behavior: By default, Excel automatically sorts time values in chronological order from 12:00 AM to 11:59 PM, which disrupts specialized overnight shift schedules.

Before you start

Ensure your time intervals are formatted consistently. Because Excel's Custom List feature may not accept native time serial numbers, you might need to convert your time values to text before creating the list.

Solution 1Recommended

Use Custom Lists to Sort Times as Text

Create a custom sorting sequence by manually defining the order of your time intervals in Excel's Custom Lists settings.

Excel allows you to define custom sorting rules for non-alphabetical and non-chronological data. To force the times to sort from 9:00 PM onwards, you must feed the exact sequence into the Custom Lists dialog. Storing these entries as text ensures Excel processes the sequence exactly as you typed it.

Convert time entries to text

Format your time intervals (e.g., '9:00 PM', '9:30 PM') as text rather than native time formats so they can be read by the custom list function.

Navigate to Edit Custom Lists

Click on 'File' in the top ribbon, select 'Options', and go to the 'Advanced' tab. Scroll down to the 'General' section and click the 'Edit Custom Lists' button.

Create the custom sequence

In the 'List entries' box, type your 30-minute intervals in order, starting with 9:00 PM, 9:30 PM, 10:00 PM, and ending with 8:30 PM. Click 'Add', then 'OK'.

Apply the custom sort

Select the data range you want to sort. Go to the 'Data' tab, click 'Sort', choose the column containing your times, and under the 'Order' dropdown, select 'Custom List...' to apply your new 9:00 PM sequence.

Tip for fast data entry: Instead of typing all 48 half-hour intervals manually in the dialog box, you can type them into a spreadsheet column first, select the range, and use the 'Import' button within the Edit Custom Lists dialog.

Efficient Data Management with WPS

Sort Custom Time Intervals Easily in WPS Spreadsheet

WPS Office provides a highly capable Spreadsheet tool that allows you to easily create custom lists for sorting specialized time sequences, shift schedules, and text-based data.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the document containing your time data.
2. Access Custom Lists: Go to 'Menu' in the top-left corner, click 'Options', and select 'Custom Lists' from the settings window.
3. Add your 9:00 PM sequence: Type your customized time intervals starting from 9:00 PM into the 'List entries' box and click 'Add'.
4. Sort the data: Highlight your data, navigate to the 'Data' tab, click 'Sort', and choose your newly added list under the 'Custom' order option.

Fully compatible with Microsoft Excel (.xlsx and .xls) formatsBuilt-in Custom List feature for flexible, non-standard data sortingFamiliar interface requires no learning curve for Excel usersFree and lightweight office suite for Windows, Mac, and Linux

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I add normal time values directly to an Excel custom list?

Excel's custom lists feature works best with text values. Standard time formats are actually stored as decimal serial numbers behind the scenes, which Excel struggles to process as distinct list items. Converting them to text resolves this issue.

How do I quickly convert existing time values to text?

You can use the TEXT function. For example, enter '=TEXT(A2, "h:mm AM/PM")' in a new column. Then, copy the results and use 'Paste as Values' over your original data to turn them into pure text strings.

Will this custom 9:00 PM sort order affect other workbooks?

Yes, custom lists are saved at the application level. Once you add the 9:00 PM sequence in Excel or WPS Spreadsheet, it will be available for sorting in any other workbook you open on that specific computer.

How do I remove the custom time sort list later?

Go back to the 'Edit Custom Lists' menu (File > Options > Advanced), select your 9:00 PM sequence from the 'Custom lists' box on the left, and click the 'Delete' button.

Office Quick Solutions