You type a date into an Excel cell and instead of treating it as a date, Excel leaves it as plain text — left-aligned, unformatted, and unable to be used in date calculations, sorting, or filtering. Or perhaps you have imported a spreadsheet full of dates from another system and none of them are recognised. This is one of the most frustrating everyday Excel issues, especially for anyone working with international date formats.
Here is why Excel fails to recognise dates and exactly how to fix it.
Why Excel Does Not Recognise Some Dates
Excel recognises dates based on your computer’s regional settings. If your system is set to UK date format (DD/MM/YYYY) but someone sends you a file with US dates (MM/DD/YYYY), Excel may misinterpret or reject them entirely. Dates stored as text strings — such as those imported from a CSV, website, or database — are another common cause.
Check Whether the Date Is Stored as Text
The quickest way to check is to click on the cell and look at the alignment. Genuine dates are right-aligned by default, while text is left-aligned. You can also check the formula bar: if the date shows as 18/11/2024 but Excel treats it as text, you will not be able to use it in formulas like DATEDIF or EDATE.
Another tell-tale sign is a small green triangle in the top-left corner of the cell, which indicates a number stored as text.
Convert Text to Dates Using the Green Triangle
If you see the green error indicator:
- Select the cell (or select all affected cells).
- Click the yellow warning icon that appears next to the selection.
- Select Convert to Number.
Excel will attempt to convert the text string into a proper date value. This works well when the text format matches your regional date format.
Use Text to Columns to Force Date Recognition
This is one of the most reliable methods for converting text dates in bulk:
- Select the column containing the problem dates.
- Go to Data → Text to Columns.
- Choose Delimited and click Next.
- Untick all delimiters and click Next.
- In the Column data format section, select Date and choose the correct format from the dropdown (e.g. DMY for UK dates).
- Click Finish.
Excel will re-parse every value in the column as a date using the format you specified. This is especially useful when working with CSV files from other countries.
Use DATEVALUE to Convert Text Dates in a Formula
If you prefer a formula-based approach, the DATEVALUE function converts a text string into a date serial number:
=DATEVALUE(A1)
This works when the text in A1 is in a format Excel can interpret, such as “18 Nov 2024” or “18/11/2024” (if your system uses DD/MM/YYYY). Format the result cell as a Date to see the human-readable date.
For more complex text formats, you may need to rearrange the parts using MID, LEFT, and RIGHT functions before passing them to DATEVALUE.
Use the DATE Function to Build Dates from Components
If the date is split across columns (day in one, month in another, year in a third) or the text format is non-standard, use the DATE function:
=DATE(year, month, day)
For example, if the year is in A1, month in B1, and day in C1:
=DATE(A1, B1, C1)
This creates a proper Excel date value regardless of the original text format.
Check Your Regional Date Format Settings
If Excel consistently misreads dates, your Windows regional settings may not match the date format you are using:
- Go to Settings → Time & Language → Language & Region.
- Click Regional format and then Change formats.
- Check the Short date format. For UK dates, it should be dd/MM/yyyy.
If the short date format is set to MM/dd/yyyy (US format), Excel will interpret “05/11/2024” as May 11th rather than 5th November. Changing this to the UK format ensures Excel interprets dates correctly when you type them.
Remove Leading or Trailing Spaces
Sometimes dates imported from other systems contain invisible spaces before or after the date text. These spaces prevent Excel from recognising the value as a date.
Use the TRIM function to remove extra spaces:
=DATEVALUE(TRIM(A1))
Or use CLEAN in combination with TRIM to remove non-printable characters as well:
=DATEVALUE(TRIM(CLEAN(A1)))
Replace Non-Standard Separators
If the imported dates use hyphens (18-11-2024), dots (18.11.2024), or other separators that Excel does not expect, use SUBSTITUTE to replace them with the separator your system uses (usually a forward slash for UK dates):
=DATEVALUE(SUBSTITUTE(A1, "-", "/"))
Format the Cell as a Date
Sometimes Excel recognises the date internally but displays the serial number (a large integer like 45614) instead of a formatted date. Right-click the cell, select Format Cells, go to the Number tab, select Date, and choose your preferred date format. The cell should now display the date correctly.
Use Find and Replace as a Quick Fix
A surprisingly effective trick: if dates are stored as text, select all the date cells, press Ctrl + H (Find and Replace), type a forward slash (/) in both the “Find what” and “Replace with” fields, and click Replace All. This forces Excel to re-evaluate each cell, and it often converts the text dates into proper dates automatically.