To fix dates Excel treats as text, first confirm the problem with =ISNUMBER(A2): a real date returns TRUE, text returns FALSE. Then convert according to the format. =DATEVALUE(A5) handles standard text dates, =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)) handles YYYYMMDD, =DATEVALUE(SUBSTITUTE(A4, ".", "/")) handles dot separators, and =DATEVALUE(LEFT(A6, 10)) strips a trailing time stamp.
You import data from a CSV, a database export, or a third-party system and the dates look fine, but Excel treats them as text. They won’t sort chronologically, formulas like NETWORKDAYS return errors, and Pivot Tables can’t group them by month. The dates are there, Excel just doesn’t recognize them. Here’s how to fix every common variation.
How to Tell If Dates Are Text
Three quick checks:
1Alignment: real dates right-align by default. Text left-aligns. If your “dates” are hugging the left edge of the cell, they’re text.
2Green triangle: Excel sometimes shows a small green triangle in the top-left corner of cells containing numbers or dates stored as text.
3ISNUMBER test: enter =ISNUMBER(A2) in a helper column. Real dates return TRUE. Text dates return FALSE. A number such as 20260315 also returns TRUE, though it is not a date, so check the value too.
The text date formats you will actually meet
| A | B | |
|---|---|---|
| 1 | Text Date | Problem |
| 2 | 20260315 | YYYYMMDD with no separators |
| 3 | 15/03/2026 | DD/MM/YYYY on a US system |
| 4 | 2026.03.15 | Dots instead of slashes |
| 5 | Mar 15, 2026 | Text month name |
| 6 | 03-15-2026 14:30 | Date with time stamp |
Fix 1: DATEVALUE (For Standard Text Formats)
=DATEVALUE(A5)
Converts recognizable text dates like “Mar 15, 2026” or “3/15/2026” into real Excel dates. Format the result cell as a date. Works for most common text date formats that match your system locale.
Locale dependent
DATEVALUE interprets dates based on your Windows regional settings. On a US system, “03/15/2026” means March 15. On a UK system, it means the 3rd of the 15th month, which doesn’t exist. If your data comes from a different locale, you need manual parsing.
Fix 2: MID/LEFT/RIGHT (For YYYYMMDD Format)
Compact date formats like “20260315” need to be parsed manually:
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))
LEFT(A2,4) extracts “2026” (year) | MID(A2,5,2) extracts “03” (month) | RIGHT(A2,2) extracts “15” (day). The DATE function assembles them into a real date.
Fix 3: SUBSTITUTE (For Non-Standard Separators)
Dots, dashes, or other separators just need to be swapped for slashes:
=DATEVALUE(SUBSTITUTE(A4, ".", "/"))
Converts “2026.03.15” to “2026/03/15”, then DATEVALUE converts it to a real date.
Fix 4: DD/MM/YYYY on a US System
This is the trickiest case: “15/03/2026” looks like a date but Excel on a US system reads it as “the 15th month” and chokes. Parse it manually:
=DATE(RIGHT(A3,4), MID(A3,4,2), LEFT(A3,2))
Extracts day, month, and year from their positions and reassembles in the correct order.
Fix 5: Date with Time Stamp
When the date includes a time (“03-15-2026 14:30”) and you just need the date:
=DATEVALUE(LEFT(A6, 10))
Grabs the first 10 characters (the date portion) and converts. If you need to keep the time too, use =DATEVALUE(LEFT(A6,10)) + TIMEVALUE(MID(A6,12,5)).
Fix 6: Find and Replace (Quick Fix)
Sometimes the simplest approach works. If dates use dots or dashes:
1Select the date column
2Press Ctrl+H (Find and Replace)
3Find: . (or -) → Replace: /
4Click Replace All
Excel often auto-recognizes the corrected format and converts the text to real dates.
Always verify
After any conversion, spot-check by sorting the column. If it sorts chronologically (Jan, Feb, Mar…) the dates are real. If it sorts alphabetically (“10/…” before “2/…”) they’re still text.
Common questions
Why does DATEVALUE return #VALUE!?
The text does not match a date format your Windows locale recognizes. A DD/MM/YYYY date on a US system is the usual culprit, and it needs rebuilding with DATE rather than converting.
Why do my dates show as five-digit numbers?
The conversion worked. Excel stores dates as serial numbers and the cell is still formatted as General. Format it as a date and it will display correctly.
How do I tell if a date is text without checking every cell?
Text aligns left and real dates align right by default. For a firmer answer use =ISNUMBER(A2), which returns TRUE for a real date and FALSE for text. A plain number like 20260315 returns TRUE too, so glance at the value as well.
A whole column of mixed date formats?
The formulas here each handle one format. Real exports rarely have one: some rows are DD/MM, some are YYYYMMDD, some carry a time stamp, and there is no reliable way to tell which is which by eye.
Excel Data Cleaner recognizes the mixed formats, reads an ambiguous date like 03/04/2024 in the order you pick (MDY, DMY or YMD), and puts the whole column into one format you choose, showing you every proposed change before writing. It keeps the dates stored as text so Excel can’t silently reinterpret them, ready for an import. If you need real Excel dates to calculate with, use the formulas above. Emails, phone numbers, names and addresses get their own cleaning steps too. Free up to 100 records per file.
Headed for a CRM? Add-on exporters turn the cleaned list into an import-ready file for HubSpot, Salesforce, Zoho, Pipedrive or monday.
See Excel Data CleanerWindows PC required: it runs inside Excel on Windows, not on a phone, a tablet or a Mac. Why it does not run on Mac.
When Formulas Aren’t Enough
Fixing dates in a one-time import is manageable. But many businesses deal with recurring date problems:
- Mixed formats in one column: some rows are MM/DD/YYYY, others are DD/MM/YYYY, and there’s no way to tell without context
- Multiple source systems: each vendor or department sends dates in a different format
- Thousands of records: manually checking which date format each row uses isn’t feasible
- Recurring imports: the same messy date data arrives every week or month
- Validation rules: dates outside a reasonable range (before 2020, after today) need to be flagged for review
A custom VBA date parser automatically detects formats, converts everything to consistent dates, validates ranges, and flags exceptions, handling any source system’s quirks.
“David was utterly fantastic, delivering a first-rate quality Excel app that has earned non-stop praise from our clients. Easy to work with, gets it, and delivers.”
Survey Application Client · Upwork
Dealing with Messy Data from Multiple Sources?
Tell us about your data challenges and we’ll build a cleanup tool that handles them all.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.