Excel dates not sorting correctly: how to fix it

Why text dates sort in the wrong order

A real Excel date is a number (1 January 2026 is 46023) shown in a date format, so it sorts by time. A text date is just characters. Text sorts from the first character: "01/15/2026" comes before "10/07/2025" because 0 comes before 1, regardless of the year.

The result looks almost right, sorted by month, with the years jumbled. That is the giveaway. These invoice dates came from a web export as text.

How to tell a date is text

Several quick signs. Text is left-aligned by default, real dates are right-aligned. The filter drop-down on the column offers Sort A to Z instead of Sort Oldest to Newest and does not group by year. Selecting the column shows a Count in the status bar but no Sum. Changing the number format does nothing.

For a definite answer, use ISTEXT. It returns TRUE for a text date and FALSE for a real one.

Fix 1: Text to Columns (best for a whole column)

Text to Columns converts the cells in place, without helper columns or formulas, and lets you tell Excel the order of day and month. It is the most reliable fix, especially for imports in a different regional format.

1. Select the date column (A2:A7).
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next.
4. Clear all the delimiter boxes and click Next.
5. Under Column data format, choose Date and pick the order the text uses: MDY for 01/15/2026, DMY for 15/01/2026.
6. Click Finish. The dates jump to the right of the cells. Apply a date format if needed, then sort.

Fix 2: VALUE or DATEVALUE in a helper column

=VALUE(A2) converts a text date into the date's serial number; format the result as a date. DATEVALUE(A2) does the same but only accepts text, and returns #VALUE! if the cell is already a real date. VALUE handles both, so it is safer on a column that is partly converted.

Both read the text using your computer's regional settings. On a US system "03/04/2026" becomes 4 March 2026. On a UK system the same text becomes 3 April, and "01/15/2026" fails with #VALUE! because there is no month 15. If your data comes from a different region, use Fix 3. After converting, copy the helper column and use Paste Special > Values over the original.

Fix 3: DATE with text functions (any region)

When the text order does not match your settings, take the pieces apart yourself. The European system in column E writes 28.02.2026. RIGHT takes the year, MID the month, LEFT the day, and DATE builds a real date from them. This works the same on every computer.

Adjust the positions to your format: for US text like 01/15/2026, use =DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)). If days and months are not always two digits (3/5/2026), Text to Columns is easier.

Still sorting oddly after converting?

Mixed columns are the usual culprit. If some cells converted and others did not, an ascending sort puts all the real dates first and the text ones after them, because Excel sorts numbers before text. Run ISTEXT down the column and convert the stragglers.

Also check that you sorted the whole table: when Excel asks, choose Expand the selection, or the dates are sorted while customers and amounts stay where they were. Leading spaces from imports keep a date as text too; remove them with TRIM before converting.