How to Fix Dates in Excel (Text to Real Dates)
Dates arrive as text in a dozen formats, and Excel's own tools guess day-first or month-first from your locale — silently swapping days and months. Here's why, and the one-click way that parses them deterministically.
Last updated: August 2026
| Before | After | |
|---|---|---|
| 13/02/2026 (text) | → | 2026-02-13 (real date) |
| Feb 13 2026 | → | 2026-02-13 |
Why DATEVALUE guesses wrong
The built-in fixes are DATEVALUE and Text to Columns, but both decide day-first versus month-first from your machine's locale. So 03/04/2026 is read as 3 April on a UK PC and 4 March on a US one — the days and months are silently swapped and nothing warns you. DATEVALUE also outright fails on many month-name and mixed formats, returning #VALUE!. When a column mixes styles, no single formula copes.
The 1-click way — Tellsheet's Fix Dates
Select the column and run Fix Dates. It parses each cell deterministically into a real Excel date — slashes, dashes, and month names alike — and a day-first switch tells it your data is D/M/Y so nothing is decided by locale. Cells it can't confidently read are left as-is, never guessed, so you can spot the stragglers rather than end up with a wrong date. The output is a genuine date you can sort, filter, and compute on. Pairs with Convert Text to Date and Extract Date Parts.
Frequently asked questions
How do I convert text to real dates?
Select the column and run Fix Dates. It turns mixed date text into real Excel dates deterministically, without the locale guessing DATEVALUE depends on.
Why does DATEVALUE swap days and months?
It reads day-first or month-first from your locale, so 03/04/2026 differs by machine. Fix Dates has an explicit day-first switch instead.
Does it read month names?
Yes — Feb 13 2026 and 13 February 2026 are recognised alongside numeric formats and resolve to the same date.
What about cells it can't read?
They're left untouched — it never guesses, so you can find and fix the odd ones yourself.
Related Excel guides
Fix a whole column of dates in one click
Fix Dates parses mixed date text into real Excel dates deterministically — with a day-first switch and month-name support.
Get Tellsheet free See pricing