Excel Dates Not Recognised as Dates
Look at the alignment. Real dates sit on the right of the cell; text dates sit on the left. That one glance tells you which problem you have, before you change anything.
If they are text, formatting them as a date will not help — the cell holds a string, and changing its format does not convert it. You have to re-parse the values, and Data → Text to Columns is the fastest way for a whole column.
This is the most common spreadsheet frustration there is: dates that look correct, refuse to sort chronologically, and return errors in every date calculation. The reason is that Excel stores dates as numbers, and what you are looking at is text pretending.
How Excel actually stores dates
A date in Excel is a serial number — a count of days. In the standard Windows system, day 1 is 1 January 1900, so 1 January 2026 is 46023. Times are the fractional part: 0.5 is midday.
The date you see is that number wearing a display format. Formatting changes only the appearance — the underlying number never moves.
This is why sorting, filtering and arithmetic all work on real dates: Excel is comparing numbers. And it is why they all fail on text dates: it is comparing strings, alphabetically.
The 60-second test. Select a suspect cell and set its format to General.
- It turns into a number like 46023 → it is a real date. Set the format back and you are done.
- It still reads "01/03/2026" → it is text. Continue below.
Why the values arrived as text
- Imported from CSV, a database export or a web page. By far the most common source.
- Regional format mismatch. A file written as DD/MM/YYYY opened on a machine set to MM/DD/YYYY. Excel converts what it can and leaves the rest as text — which is why some rows convert and others do not. Anything above the 12th of the month cannot be misread as a month, so it stays text while 01–12 silently convert to the wrong date.
- Leading or trailing spaces. " 01/03/2026" is not a date to Excel.
- Non-breaking spaces from a web copy — character 160, which looks identical to a normal space and is not one.
- An unrecognised format — "1st March 2026", "2026.03.01", "March 1 '26".
The dangerous case is the partial conversion. When a DD/MM file lands on an MM/DD machine, rows 01–12 convert to valid but wrong dates while rows 13–31 remain text. Nothing looks broken. If you are working with a mixed column, check a known date before trusting any of it.
Fix 1: Text to Columns (best for a whole column)
Fastest reliable method, and it handles a full column in one pass.
- Select the column. One column at a time — this tool cannot do several.
- Data → Text to Columns.
- Choose Delimited, click Next.
- Untick every delimiter, click Next.
- Under Column data format choose Date, and set the dropdown to the order the text is currently in — DMY for 01/03/2026 meaning 1 March.
- Click Finish.
Step 5 is the one that matters. You are telling Excel how to read what is there, not how you want it displayed. Choose wrong and you get valid dates with the day and month swapped, which is worse than an obvious failure because nothing flags it.
Fix 2: DATEVALUE (when you want a formula)
In a helper column: =DATEVALUE(A2) returns the serial number, which you then format as a date.
It respects your machine's regional settings, so it can misread ambiguous DD/MM text the same way. For unambiguous control, build the date explicitly from its parts:
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))
That reads "01/03/2026" as year 2026, month 03, day 01 regardless of regional settings — because you have specified which characters mean what.
Fix 3: Clean the invisible characters first
If Text to Columns and DATEVALUE both fail on values that look perfectly normal, something invisible is attached. Non-breaking spaces from web pastes are the usual culprit, and TRIM does not remove them because they are character 160, not 32.
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
That converts non-breaking spaces to ordinary ones and then strips them. Run the result through DATEVALUE, or paste it back as values and use Text to Columns.
Dates that will not sort correctly
Almost always the same root cause. Text sorts alphabetically, so "01/12/2025" comes before "02/01/2020" — the sort is comparing the first character, not the date.
Convert them properly using any method above and sorting fixes itself. If some rows sort correctly and others do not, you have a mixed column: part converted, part text. Select the whole column, set it to General, and see which cells show numbers.
The 1900 leap year quirk
Worth knowing if you ever work with very old dates. Excel treats 29 February 1900 as a real date. It never existed — 1900 was not a leap year.
Microsoft documents this deliberately: the bug was inherited from Lotus 1-2-3 for file compatibility, and fixing it would have broken every existing spreadsheet. It means date arithmetic spanning 28 February 1900 is off by one day. For any date after March 1900 — which is all practical work — it has no effect.
If leading spaces are what stops a date being recognised, how to remove spaces in Excel covers clearing them from a whole column, including the invisible non-breaking kind.
Text to Columns appears again in how to split cells in Excel, which covers the same wizard used for separating data rather than converting it.
Frequently asked questions
Why is my Excel date not recognised as a date?
The cell contains text that looks like a date. Check the alignment: real dates align right, text aligns left. Formatting it as a date will not convert it — use Data → Text to Columns and set the column format to Date with the correct order.
Why won't my dates sort correctly in Excel?
Because they are text, and text sorts alphabetically rather than chronologically. Convert them to real dates and sorting works. If only some rows misbehave, the column is mixed — part converted, part still text.
I formatted the cells as Date and nothing changed
Formatting only changes how a value is displayed; it does not convert text into a number. The cell still holds a string. You have to re-parse the values with Text to Columns, DATEVALUE, or a DATE formula.
How do I convert text to dates in Excel?
Select the column, choose Data → Text to Columns, pick Delimited, untick all delimiters, then set Column data format to Date and choose the order the text is currently in (DMY for 01/03/2026 meaning 1 March). Click Finish.
Why did some of my dates convert and others stay as text?
A regional mismatch. On a machine expecting MM/DD, values with a first number of 01–12 are accepted as valid (but wrong) dates, while anything from 13 upward cannot be a month and stays as text. Verify against a date you know before trusting the column.
What number does Excel use for a date?
A serial count of days from 1 January 1900, which is day 1. Times are the fraction of a day, so 0.5 is midday. Setting a date cell to General format reveals the number.
Why does TRIM not fix my dates?
TRIM only removes standard spaces (character 32). Text copied from web pages often contains non-breaking spaces (character 160), which look identical. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to remove both.
Conclusion
Check the alignment first — right means a real date, left means text — and remember that formatting never converts. Text to Columns handles almost every case in one pass, provided you tell it the order the text is currently in.
And be careful with mixed columns from regional mismatches. A silently swapped day and month is the one failure here that does not announce itself.
Related: why an Excel formula is not calculating covers the text-format trap in formulas, and Excel formula errors explained covers the error codes.
Comments