Calculation Issues

How to Sort AM and PM Time Values Chronologically in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to sort time entries with AM/PM designations in chronological order instead of alphabetical order.

Product: Excel
Device & OS: not provided
Scenario: Organizing a list of times (e.g., 12 AM, 1 AM, 2 AM, 11 PM) sequentially, or utilizing them accurately within a PivotTable.
Observed behavior: Time values are being sorted alphabetically (e.g., 1 AM, 10 AM, 11 AM, 12 AM) because the system is interpreting the values as text strings rather than actual time values.

Before you start

Select your column of time values and check the formatting in the Home tab. If the values align to the left side of the cell by default, they are likely stored as text and must be converted to numerical time values before sorting.

Solution 1Recommended

Convert Text Times to True Time Values Using a Helper Column

Use the TIMEVALUE function in a helper column to convert text-based time strings into Excel-recognized chronological time values.

By extracting the hour and the AM/PM designation from the text string, we can force Excel to recognize the cell as a valid time format.

Insert a helper column

Right-click the column header next to your time values and select 'Insert' to create a blank helper column.

Apply the TIMEVALUE formula

In the first cell of your helper column (e.g., B2, assuming your text times are in column A), enter the formula: =TIMEVALUE(LEFT(A2,LEN(A2)-2)&":00 "&RIGHT(A2,2)) and press Enter. Drag the fill handle down to apply this formula to the rest of the column.

Paste as values

Select the newly calculated cells in the helper column, press Ctrl+C to copy them, right-click the selection, and choose 'Paste as Values' (the clipboard icon with 123) to remove the underlying formula.

Format as Time

With the values still selected, go to the 'Home' tab, click the Number Format dropdown, and choose 'Time' or create a Custom format of 'h AM/PM'.

Sort the data

Select your entire dataset, go to the 'Data' tab, click 'Sort', and choose to sort by your new helper column from Oldest to Newest. If you are using a PivotTable, update its data source to use this new column.

Tip: Once your data is sorted chronologically, you can safely hide or delete the original text-based time column to keep your spreadsheet clean.

WPS Spreadsheet Solution

Sort Time Values Chronologically with WPS Spreadsheet

WPS Spreadsheet provides identical data sorting capabilities and formula functions as Excel, making it easy to convert text to time and sort your schedules chronologically.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your time data.
2. Format using the helper column: Insert a helper column and use the TIMEVALUE formula to convert your text strings into accurate time values.
3. Sort chronologically: Navigate to the 'Data' tab, click 'Sort', and arrange your newly formatted times chronologically with a single click.

100% compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Built-in advanced sorting and Text to Columns features for quick data cleaning.Free, lightweight, and available on Windows, Mac, Linux, and Mobile.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel sort my times alphabetically instead of chronologically?

Excel sorts data alphabetically when it recognizes the cell contents as text rather than numerical time values. This frequently occurs when data is imported from external software, exported from web forms, or typed manually without the standard spacing between the hour and the AM/PM designation.

Can I fix text times without using a helper column?

Yes. You can select your data, go to the 'Data' tab, and click 'Text to Columns'. Choose 'Delimited', click Next until you reach the final step, and select 'General' or 'Date' (depending on your regional settings), then click Finish. This often forces Excel to re-evaluate the text as actual time values.

How do I display times without minutes or seconds after converting them?

Select the converted time cells, press Ctrl+1 to open the Format Cells dialog, navigate to the 'Custom' category, and enter 'h AM/PM' in the Type box. This will display only the hour while maintaining the precise chronological value in the background.

Excel & Data Problems