How to fix dates stored as text in Excel
Causes and fixes
- Dates were pasted or imported as text
- The day and month are in a different order from your settings
- Dates are written with dots or extra text
- Set the date type when importing
1Dates were pasted or imported as text
Dates from websites, PDFs and exports often arrive as text. They look right but sort alphabetically, ignore date formats and break formulas such as DATEDIF and SUMIFS.
How to recognise it
- The dates line up on the left of the cell.
=ISTEXT(A2)returns TRUE.- Sorting puts 10/1 before 2/1.
Fix
Use Data > Text to Columns, click Next twice, choose Date and the order of the parts, and click Finish. Or convert in a helper column with =DATEVALUE(A2) or =--A2 and format the result as a date.
2The day and month are in a different order from your settings
A file from another country may write 03/04/2026 for 3 April. With US settings, Excel reads it as March 4, and dates like 13/04/2026 stay as text because there's no 13th month.
How to recognise it
Some dates in the column convert and others don't, or months and days look swapped.
Fix
Use Text to Columns with DMY, or build the date from its parts:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))This works for text in the fixed form dd/mm/yyyy.
3Dates are written with dots or extra text
2026.04.03, "Apr 3, 2026 10:15 AM" or 2026-04-03T10:15:00 may not convert on their own.
Fix
- Dots: select the column and use Find & Replace to replace
.with-, or use=--SUBSTITUTE(A2,".","-"). - ISO date with time:
=--LEFT(A2,10)keeps the date part. - Leading spaces:
=--TRIM(A2).
4Set the date type when importing
When you import a CSV, you can tell Excel which country's date order the file uses.
Fix
Use Data > From Text/CSV > Transform Data, right-click the date column and choose Change Type > Using Locale, then pick Date and the file's locale.
Still stuck?
- After converting, give the column a date format with Ctrl+1; a number like 46115 is a date without its format.
- Converted dates still showing on the left? They are still text; check for spaces with
=LEN(A2). - Dates as text make SUMIFS return 0. See SUMIFS returns 0.