Concept
1. The problem
Data exported from banking portals, Tally/ERP software, GST portals or websites often has dates that look right but are text. Then sorting goes wrong, SUMIFS by date returns 0, subtraction gives #VALUE!, and pivot tables can't group by month.
2. How to spot it
- Real dates are right-aligned by default; text dates are left-aligned.
=ISNUMBER(A2)returns FALSE for a text date.- Change the format to General (Ctrl + Shift + ~): a real date becomes a number like 46296; text stays the same.
3. The silent trap: dd/mm vs mm/dd
If your Windows region is set to US (mm/dd/yyyy) and the data is Indian format (dd/mm/yyyy):
05/08/2026(5 August) is converted to 8 May — wrong, but it looks like a valid date.25/08/2026can't be a month 25, so it stays text.
So you end up with a column where some dates are wrong and some are text. Always check a date with a day above 12 after importing.
4. Fix 1: Text to Columns (best for most cases)
- Select the date column.
- Data → Text to Columns → Delimited → Next → Next.
- Under Column data format choose Date: DMY (the format your data is in).
- Finish.
This converts in place and correctly handles the dd/mm order, regardless of the system setting.
5. Fix 2: build the date with a formula
When the text has a fixed layout, cut it with the Lesson 1 functions and put it together with DATE(year, month, day).
| Text in A2 | Formula |
|---|---|
15/08/2026 or 15.08.2026 |
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) |
20260815 |
=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)) |
2026-08-15 |
=DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2)) |
This only works if day and month are always two digits (05, not 5). For mixed data, TEXTSPLIT on / gives day, month and year separately (Microsoft 365 / Excel 2024).
6. Fix 3: extra spaces or dots
Sometimes the only problem is a space or a dot. Clean it and convert:
=--TRIM(A2) → removes spaces, converts
=--SUBSTITUTE(A2, ".", "/") → 15.08.2026 → date (if system is DMY)
The -- (double minus) converts text to a number. These depend on your system date setting, so verify the result.
7. Fix 4: DATEVALUE
=DATEVALUE(A2)
Works only when the text matches your system's date format. Fine for clean text like 15-Aug-2026 (month as a word is unambiguous), risky for 05/08/2026.
8. After fixing
Format the result as a date, check with =ISNUMBER(), then Copy → Paste Special → Values over the original column if you want to remove the formulas.
DATE normalizes out-of-range day/month values rather than rejecting them, so validate the source components before using it on untrusted data. Month names such as Aug also require a compatible language setting.
Common mistakes
Trusting dates that "look" right after import. Using DATEVALUE on numeric dd/mm data. Fixing the format (Ctrl + 1) and expecting text to become a date — formatting doesn't change the data type.