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.
The Usual Suspects
| A | B | |
|---|---|---|
| 1 | Text Date | Problem |
| 2 | 20250315 | YYYYMMDD with no separators |
| 3 | 15/03/2025 | DD/MM/YYYY on a US system |
| 4 | 2025.03.15 | Dots instead of slashes |
| 5 | Mar 15, 2025 | Text month name |
| 6 | 03-15-2025 14:30 | Date with time stamp |
Fix 1: DATEVALUE — For Standard Text Formats
=DATEVALUE(A5)
Converts recognizable text dates like “Mar 15, 2025” or “3/15/2025” 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/2025” 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 “20250315” need to be parsed manually:
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))
LEFT(A2,4) extracts “2025” (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 “2025.03.15” to “2025/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/2025” 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-2025 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.
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.
Start a Project