How to why excel dates don't sort correctly and how to fix them
- Step 1Confirm the dates are text — Text dates left-align by default and sort alphabetically; true dates right-align and sort chronologically. If sorting is wrong, they're almost certainly text.
- Step 2
- Step 3Drop the workbook — Add the
.xlsxor.csvcontaining the unsortable column. - Step 4Pick ISO 8601 — Choose
ISO 8601 (yyyy-MM-dd)— this is the format whose text order matches date order, which is what fixes sorting. - Step 5Run and reopen in Excel — Download
dates-standardized.xlsx, open it, and sort the column — it now sorts chronologically. - Step 6Re-convert to real dates if you need date math — If you need DATEDIF/NETWORKDAYS, select the now-uniform column in Excel and convert text to dates (e.g. Text-to-Columns), which works easily once every value is identical in shape.
Why text dates sort wrong — and which output fixes it
Text sorts character by character. Only ISO 8601 has the property that alphabetical order equals chronological order, which is why it's the right pick for sorting fixes.
| Stored as text | Alphabetical (broken) order | ISO 8601 rewrite | Sorts chronologically as text? |
|---|---|---|---|
01/02/2024, 01/10/2024, 01/11/2024 | 01/02, 01/10, 01/11 | 2024-02-01, 2024-10-01, 2024-11-01 | Yes |
9/29/2024, 10/1/2024 | 10/1 before 9/29 | 2024-09-29, 2024-10-01 | Yes |
Dec 5 2023, Jan 5 2024 | Dec before Jan | 2023-12-05, 2024-01-05 | Yes |
UK output dd/MM/yyyy | 01/02, 01/10... | 01/02/2024, 01/10/2024 | No — day-first text still sorts wrong |
What chrono-node does with common date strings
Behaviour verified by running the bundled chrono-node parser. The parser uses its default English settings — month-first for ambiguous slash dates — and there is no locale switch in this tool.
| Input cell value | Parses? | Result (to ISO 8601) | Note |
|---|---|---|---|
2024-09-29 | Yes | 2024-09-29 | Already ISO — passes through unchanged |
09/07/2024 | Yes | 2024-09-07 | Read as month-first (US): Sep 7, not 9 July |
13/07/2024 | Yes | 2024-07-13 | Day-first used only because 13 cannot be a month |
29 September 2024 | Yes | 2024-09-29 | Natural-language text date |
29-Sep-24 | Yes | 2024-09-29 | Two-digit year is expanded |
2024-09-29 14:30 | Yes | 2024-09-29 | Time is parsed then dropped by the date-only token |
next friday | Yes | (date relative to today) | Relative phrases resolve against the run date |
Q3 2024 | No | (left unchanged) | Listed in the findings report |
45899 (number as text) | No | (left unchanged) | A bare serial typed as text does not parse |
Tier limits for the Date Standardizer
The Date Standardizer is gated to Pro and above — a Free account cannot run it. Limits below are the real Excel-family numbers. One file is processed per run.
| Tier | Max file size | Max rows | Runs this tool? |
|---|---|---|---|
| Free | 5 MB | 10,000 | No — Pro required |
| Pro | 50 MB | 100,000 | Yes |
| Pro-media | 200 MB | 500,000 | Yes |
| Developer | 500 MB | Unlimited | Yes |
Cookbook
Before/after sort orders. The 'before' column is text that Excel sorts alphabetically; the 'after' column is the same dates rewritten to ISO 8601, which sorts chronologically even though it's still text.
The classic alphabetical sort, fixed
Text dates sorted ascending land in alphabetical order. After ISO 8601 rewrite, an ascending sort is finally chronological.
Before sort (text): After ISO 8601, sort ascending: 01/02/2024 2024-02-01 01/10/2024 2024-10-01 01/11/2024 2024-11-01 01/20/2024 2024-... (chronological)
Single-digit months break text sort the worst
Without zero-padding, 10/1 sorts before 9/29 as text. ISO 8601 zero-pads every component, so this stops happening.
Before (text, sorted): After (ISO 8601, sorted): 10/1/2024 2024-09-29 9/29/2024 2024-10-01 9/3/2024 2024-... correct order
Spelled-out months sort by letter, not time
Dec 5 2023 sorts before Jan 5 2024 because D < J. Converting to ISO fixes the ordering.
Before (text): After (ISO 8601): Dec 5 2023 2023-12-05 Jan 5 2024 2024-01-05 Feb 1 2024 2024-02-01
Don't pick a regional format if you care about sorting
Choosing UK or US output keeps the day/month at the front, so the text still sorts wrong. ISO 8601 is the only format that fixes sorting.
Output = UK (dd/MM/yyyy): still sorts alphabetically wrong 01/02/2024, 01/10/2024 ... Output = ISO 8601: sorts chronologically 2024-02-01, 2024-10-01 ...
Mixed text and real dates in one column
If some cells are already true dates and others are text, the tool reformats both to ISO text, giving you one uniform, sortable column.
Before (mixed): 2024-09-29 (true Excel date) 10/1/2024 (text) Dec 5 2024 (text) After (all ISO 8601 text): 2024-09-29 2024-10-01 2024-12-05
Edge cases and what actually happens
Output is still text, not a true date
By designThe rewritten cells are strings. With ISO 8601 they sort chronologically as text — which solves the sorting complaint — but they are not Excel serial dates. Re-convert in Excel if you need date math.
You picked UK or US output
Sort still brokenDay-first and month-first text still sorts alphabetically by the leading number. Only ISO 8601 fixes sorting. Re-run with ISO selected.
Single-digit months/days without padding
FixedISO 8601 zero-pads to 2024-09-05, eliminating the 10/1 < 9/29 text-sort bug.
A few cells fail to parse
Failed parseUnparseable values (TBD, Q3 2024) stay as-is and are listed in failedCells. They'll still sort oddly until you fix them at source.
Excel serial number stored as text
Failed parseA bare serial like 45899 typed as text doesn't parse. Make it a real date in Excel first, then re-run.
Column has times appended
Time dropped2024-09-29 14:30 converts to 2024-09-29; the time is discarded. The date portion still sorts correctly.
Auto-detect misses a sparse column
Possible missIf under 60% of the first 20 non-empty sampled rows parse as dates, the column isn't auto-detected. Name it explicitly in the columns box to force the scan.
Already-ISO cells re-sorted
SafeCells already in yyyy-MM-dd are rewritten identically and continue to sort correctly. Re-running is harmless.
Workbook has dates on a non-first sheet
Not processedOnly the first sheet is processed. Move the date column to sheet 1 before running.
File over tier limit
RejectedFiles above your tier ceiling (50 MB / 100,000 rows on Pro) are rejected before processing.
Frequently asked questions
Why do my Excel dates sort alphabetically?
Because they're stored as text, not as true Excel dates. Excel sorts text character by character, so 01/10 comes before 01/02. Converting the column to ISO 8601 makes the text sort chronologically.
How do I tell if my dates are text?
Text dates left-align in the cell by default; true dates right-align. The giveaway is the symptom itself: if sorting produces alphabetical rather than chronological order, they're text.
Why does ISO 8601 fix sorting when it's still text?
For yyyy-MM-dd, alphabetical order is identical to chronological order — 2024-02-01 < 2024-10-01 both as text and as dates. That's the whole trick. Regional formats don't have this property.
Will DATEDIF and NETWORKDAYS work on the output?
Not directly — the output is text, so Excel won't treat it as a date for math. But because every value is now uniform ISO 8601, converting the column back to real dates in Excel (e.g. Text-to-Columns) is trivial, and then those functions work.
Should I pick UK or US output to fix sorting?
No. Day-first and month-first text still sorts alphabetically by the leading number. Use ISO 8601 — it's the only format that fixes sorting.
What if only some cells are text and others are real dates?
The tool reformats both to ISO 8601 text, giving you one uniform, correctly-sorting column. The real-date cells are decoded by SheetJS first, then reformatted.
Do I need helper columns or DATEVALUE?
No. The tool rewrites the whole column in one pass without any formulas or helper columns.
Which cells won't convert?
Values chrono can't parse — quarters (Q3 2024), placeholders (TBD), bare serial numbers stored as text. They stay as-is and are listed in the findings report.
Does it find the date column automatically?
Yes, if at least 60% of the first 20 non-empty rows parse as dates. Otherwise, name the column in the comma-separated box.
Is the file uploaded?
No — everything runs in your browser. The workbook is never sent anywhere.
How many rows can it handle?
100,000 on Pro, 500,000 on Pro-media, unlimited on Developer. It's a Pro-tier tool.
What about times in the date cells?
The date portion is kept and the time is dropped, since the output formats are date-only. Sorting on the date still works.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.