Dates sort wrong in Excel because some of your “dates” are real date values and some are text that only looks like a date. The fix is converting everything to one true date type, or to the ISO format YYYY-MM-DD, which sorts correctly even as text. Here is how to diagnose which problem you have and fix it.
The two-minute diagnosis
- Select the column and set alignment to General. Real dates jump right; text stays left.
- Or use
=ISTEXT(A2)in a helper column: TRUE means that cell is text pretending to be a date. - Mixed TRUE and FALSE down the column is the classic symptom: half your dates sort as numbers, half sort alphabetically, and “Apr 2024” lands before “Jan 2023.”
Why this happens
Excel stores real dates as serial numbers (January 1, 1900 is day 1, and December 25, 2023 is serial 45285) and formats them for display. When an import contains a format Excel does not recognize for your regional settings, like “2023-1-5” or “Jan 3 2024” on some systems, Excel keeps it as text. CSV files make this worse because CSV has no type information at all; every value arrives as text and Excel guesses.
Fix 1: Text to Columns (fastest built-in)
- Select the date column.
- Data > Text to Columns > Delimited > Next > Next.
- In step 3, choose Date and pick the order your TEXT currently uses (MDY for 12/25/2023, DMY for 25/12/2023). This is the step everyone gets wrong: the setting describes the input, not the output.
- Finish. Excel re-parses every cell as a real date.
Fix 2: DATEVALUE for formulas
=DATEVALUE(A2) converts recognizable text dates to serials, then you format the column as a date. It returns #VALUE! on anything Excel cannot parse, which is actually useful: filter for the errors and you have a worklist of the genuinely broken values.
Fix 3: standardize to ISO format
ISO 8601 format, YYYY-MM-DD, has a property the others lack: it sorts correctly even when stored as text, because the most significant parts come first. It is also unambiguous; 2024-04-01 cannot be misread as January 4. This is why our cleaning tool normalizes every recognized date to YYYY-MM-DD.
| Input in your file | After cleaning |
|---|---|
| 12/25/2023 | 2023-12-25 |
| 2023-1-5 | 2023-01-05 |
| Jan 3 2024 | 2024-01-03 |
| 3 Jan 2024 | 2024-01-03 |
| 12-25-2023 | 2023-12-25 |
One honest caveat that most tools hide: ambiguous slash dates like 4/1/24 are read as US month-first (April 1). If your data is day-first (UK/EU), do not run date cleaning on it yet; the assumption is documented openly in How Cleaning Works.
Keeping dates clean going forward
- Ask upstream systems to export ISO dates (most CRMs and accounting tools have the option).
- In Excel, format date columns explicitly rather than relying on General.
- When sharing CSVs across regions, ISO format is the only convention that survives the trip.
FAQ
Why does Excel show ##### in my date column?
The column is too narrow to display the date, or the cell contains a negative date serial. Widen the column first; if ##### persists, the underlying value is broken.
What is the 1900 vs 1904 date system?
Windows Excel counts from January 1, 1900; old Mac Excel counted from January 2, 1904, so the same serial number differs by 1,462 days between systems. If imported dates are uniformly off by exactly four years and a day, check File > Options > Advanced > “Use 1904 date system.”
Can I fix mixed date formats in one pass?
In Excel, only if Text to Columns can parse all the variants with one input setting, which mixed files often defeat. Our free tool recognizes the common US and textual formats in the same column and shows you exactly what changed before you apply it.
Leave a Reply