Dates & regional formats · 5 min read

Excel Dates Not Sorting Correctly? Fix Text Dates

Excel may offer A to Z instead of Oldest to Newest, or put some dates in a separate group even after you apply a Date format. Check the stored values first: date-looking text is not a numeric Excel date.

JHSOFT editorialUpdated September 9, 2026

WORKED EXAMPLESynthetic data

task_id,due_text
T1,02/10/2026
T2,15/02/2026
T3,31/12/2025

With source dates in DD/MM/YYYY, text order is T1, T2, T3. The correct oldest-to-newest order is T3 → 2025-12-31, T2 → 2026-02-15, T1 → 2026-10-02. Keep each task ID with its date.

Download the sample ↓

Check values instead of trusting alignment

Make a copy of the sheet, then choose representative cells from the top, middle, and bottom of the date column. In an empty helper column, enter =ISNUMBER(B2). TRUE means the cell contains a number, which is how Excel stores ordinary dates; it does not prove that the number represents the correct date. =ISTEXT(B2) identifies text.

Left or right alignment is only a clue because someone may have changed it manually. Likewise, the Number Format dropdown can say Date while a text value remains text. Compare at least one numeric value with a known source record before treating the whole column as ready.

What you find	What to do
All values are numeric and represent correct dates	Apply the desired display format and sort.
Text values follow a known fixed pattern	Convert in a helper column.
Text contains ambiguous slash dates	Confirm day/month order before converting.
Numbers and text are mixed	Preserve numeric dates and convert only reviewed text.
Some rows say TBD or contain errors	Resolve or separate those rows before sorting.

Convert a known day-month-year pattern

The sample assumes the three values in B2:B4 were imported as text and follow exactly DD/MM/YYYY. In C1 enter due_date. Use the formula below in C2 and fill down. It builds a date from the explicit year, month, and day components rather than asking Excel to guess the slash-date convention.

Apply the custom number format yyyy-mm-dd to C2:C4. The underlying results should now be numeric dates. Formulas here use English function names and comma separators; installations with other formula settings may require localized names or semicolons.

Convert fixed-width DD/MM/YYYY text

C2: =DATE(VALUE(RIGHT(B2,4)),VALUE(MID(B2,4,2)),VALUE(LEFT(B2,2)))
D2: =ISNUMBER(C2)
E2: =IFERROR(TEXT(C2,"dd/mm/yyyy")=B2,FALSE)

The text 02/10/2026 becomes the numeric date 2 October 2026, displayed as 2026-10-02. — Illustrated example. Synthetic example: the day-first text becomes a numeric date displayed in year-month-day order. Numeric status alone does not validate its calendar meaning.

Convert text dates with Text to Columns (DMY)

For a one-time conversion in Excel desktop, the wizard is an alternative to the formula above. Work on a copy of the date column; the source order in this sample is day, month, year. Choosing DMY describes the incoming text, not your preferred final display.

In H1 enter converted_due_date. Copy B2:B4 into the empty range H2:H4, then select those copied cells.
Open Data > Text to Columns, choose Delimited, and select Next.
Clear delimiter selections and confirm that each preview row remains one column; continue to the final step.
Select Date and DMY, confirm H2 as the destination, and finish.
Check =ISNUMBER(H2), format the results as yyyy-mm-dd, and compare all three dates with the expected output before sorting the full table.

Reject invalid dates before accepting results

DATE can roll an out-of-range day into a later month. That makes a simple component formula a converter, not a complete validator. For this exact fixed-width input, the E2 round-trip comparison should be TRUE: the converted date formatted back to DD/MM/YYYY must equal the original text.

For example, 31/02/2026 must be flagged, not accepted as a March date. Review FALSE results and formula errors before replacing any original values. If the source also permits surrounding spaces, timestamps, or single-digit months, define those cases first; the strict comparison above intentionally rejects them.

Sort the whole table and inspect the result

Select the full rectangular dataset, including task IDs and the conversion you chose. With the formula method, use due_date in column C as the sort key. With Text to Columns, include the helper column in the selection (A1:H4 for this sample) and use converted_due_date in column H. Open Data > Sort, confirm the headers, and choose Oldest to Newest for that key. If Excel asks to expand the selection, include the related record columns. Sorting just the dates can attach the wrong deadline to each task.

The expected task order is T3, T2, T1. Verify the oldest and newest rows and compare the row count with the source. Once satisfied, keep the original text for traceability or paste the converted column as values in a reviewed output copy.

Text sorting keeps T1, T2, T3 because the leading days are 02, 15, and 31. Numeric date sorting gives the correct order T3, T2, T1. — Illustrated example. Synthetic comparison: the day-first strings sort by their leading day characters; converted dates sort chronologically with complete records kept together.

Choose a conversion that fits the real column

Our day-first sample sorts lexically by its leading day characters, producing T1, T2, T3 instead of chronological order. Consistent zero-padded YYYY-MM-DD text behaves differently: it already sorts chronologically as text when compared on its own. Conversion still matters when that column mixes text with numeric dates, needs date arithmetic, or must support date filters.

For a known local date pattern, DATEVALUE may be sufficient, but its interpretation depends on system settings. For repeat CSV imports, Power Query with an explicit locale is easier to reuse. Do not wrap the final dates in TEXT if you need numeric dates afterward: TEXT creates strings. Keep display formatting on the numeric cells instead.

Common questions

Why does Excel only show A to Z instead of Oldest to Newest?

That is a useful clue that the selected date values may be text. Check a suspect cell with ISTEXT and ISNUMBER. Convert confirmed text dates, then select the converted date column as the sort key; the menu label alone does not validate the dates.

Why did changing the format to Date not fix sorting?

Formatting changes the display of an existing value; it does not reliably parse date text. Convert the text using its source order first. For this DD/MM/YYYY sample, choose DMY in Text to Columns or use the explicit DATE formula.

Why do only some dates sort correctly?

The column may mix numeric dates with text, errors, or labels such as TBD. Fill the type checks down to find the remaining text rows and convert only those whose meaning is established. If a text conversion still fails, inspect hidden spaces and confirm the date order.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →