Convert Text to Date in Excel: 3 fixes that always work

Dates that came from another system are often text: left-aligned, won’t sort, formulas give #VALUE!. Three fixes, by what the text looks like.

Fix 1: Text to Columns (dd/mm/yyyy, dd-mm-yyyy)

1. Select the column.
2. Data › Text to Columns › Next › Next.
3. Column data format: Date › pick the order the text is in — DMY for 29/09/2026, MDY for 09/29/2026. Finish.

The column becomes real dates in place. No formula needed.

Fix 2: DATEVALUE (text Excel can read)

=DATEVALUE(A1)

Works for “29 Sep 2026”, “29-Sep-26”, “2026-09-29”. Format the result cell as Date (it shows a number first). For dots — “29.09.2026” — replace them first:

=DATEVALUE(SUBSTITUTE(A1, ".", "/"))

Fix 3: DATE from pieces (20260929, or odd layouts)

=DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2))

Year, month, day pulled out by position. Adjust the positions for your layout.

Make it permanent

Copy the formula results, Paste Special › Values over the original, delete the helper column.

Check it worked

=ISNUMBER(B1) is TRUE. The cell is right-aligned. Sort Oldest to Newest puts January before September.

Common mistake

Choosing MDY when the text is DMY: 05/09 becomes 9 May instead of 5 September, silently, for every date that could go either way. Check one date you know.