Excel does not sort dates alphabetically. It sorts the serial number behind each date. If the column contains text, Excel sorts the characters instead — so 01/12/2026 can appear before 02/01/2026, and “Sort Oldest to Newest” looks broken.
Quick answer: test the column with =ISNUMBER(A2). If it returns FALSE, the date is text. Select the column, open Data → Text to Columns, choose Delimited → Next → Next, set Column data format to Date, pick DMY or MDY, then Finish. Format the result as Date with Ctrl+1 and sort again.
Match the Symptom to the Cause
| What you see | Likely cause | First fix |
|---|---|---|
| Dates sort as 1, 10, 11, 2 | Dates are stored as text | Text to Columns with the correct DMY/MDY order |
| Dates show as 45678 | Real dates formatted as numbers | Ctrl+1 → Date format; do not retype the values |
| Some dates sort, others stay put | A mix of real dates and text dates | Convert the whole column, not just the visible cells |
| Dates with times sort strangely | Different times on the same day | Use a helper column with =INT(A2) |
| PivotTable lists every date separately | Pivot source contains text dates | Convert to real dates, then refresh the pivot |
DATEVALUE returns #VALUE! | Regional day/month order does not match | Use DATE() or Text to Columns with an explicit order |
1. Confirm What Excel Thinks the Cell Contains
Right-alignment is a clue, not proof. Use a formula in a spare cell next to the date:
=ISNUMBER(A2)returns TRUE for a real date, because a date is a number.=ISTEXT(A2)returns TRUE for a text date.=ISFORMULA(A2)tells you whether the cell contains a formula rather than a pasted value.=FORMULATEXT(A2)shows the formula, or#N/Awhen the cell is a value or text.
If ISNUMBER is FALSE, converting the value is the fix. Formatting the cell as Date will not turn text into a real date.
2. Convert Text Dates in Place With Text to Columns
This is the fastest fix for a whole column and it respects the date order you choose:
- Make a copy of the column if the data is important.
- Select the entire column, including any blank cells below the data.
- Go to Data → Text to Columns.
- Choose Delimited, then click Next twice.
- Under Column data format, choose Date and select DMY or MDY to match the text.
- Click Finish, then format the column as Date with Ctrl+1 → Date.
If the preview still shows the wrong date, undo and repeat with the other order. Do not guess: a UK file read as MDY will silently swap day and month for every date where both are 12 or less.
3. Convert With Formulas When You Need an Audit Trail
A helper formula is useful when the marker wants to see how the date was built. Use the formula that matches the exact text shape:
=DATEVALUE(A2)— works when A2 is text in your regional date format. It is still locale-dependent, so it is not safe for mixed US/UK files.=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))— for fixed-widthDD/MM/YYYYtext.=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))— for fixed-widthMM/DD/YYYYtext.=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))— forYYYYMMDDtext.=--A2— a double unary that coerces recognised text dates or numbers. If it returns#VALUE!, the text is not in a format Excel recognises.
Copy the helper result and use Paste Special → Values over the original column only after you have checked several rows. Keep the original column until the submission is complete.
4. Fix Dates That Display as 45678
A five-digit number is probably a real date wearing a Number format. Select the cell, press Ctrl+1, choose Date, and pick a format. The value does not change — only the display does.
Do not “fix” it with =TEXT(A2,"dd/mm/yyyy") if you need to sort or filter. TEXT returns text, which puts you back at the start. If you need an ISO label for a report, keep a separate real-date column for sorting.
5. Fix Mixed US and UK Dates
09/11/2026 is ambiguous. In the UK and most of Europe it is 9 November. In the US it is 11 September. Excel resolves it using the Windows or macOS regional setting, not the file name or the assignment brief.
When both numbers are 12 or less, check the source. A UK bank statement, a European CSV and an Australian timetable usually use day-first. A US export usually uses month-first. If the source is inconsistent, split the column into unambiguous cases first and handle the rest manually.
The free Date Fixer flags every ambiguous pair and shows both possible dates before you commit. For repeatable imports, use Power Query and set the locale explicitly with Change Type → Using Locale.
6. Fix Dates With Times
A date-time value is a decimal: the whole number is the date, the fraction is the time. Two rows on the same day can sort in an unexpected order when their times differ. Use a helper column:
=INT(A2)
Format the helper as Date, then sort by that column. If you want to keep the time but sort by date only, use the helper as the primary sort key and the original time column as the secondary key.
7. Remove Invisible Characters and Apostrophes
Dates copied from a website or PDF often carry a leading apostrophe, a non-breaking space or a line break. The cell looks normal but Excel treats it as text. Try:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
Then convert the cleaned result with Text to Columns. The Data Cleaner can strip spaces and non-printing characters from a pasted column before it reaches Excel.
8. Fix the Sort Itself
If the values are real dates and the order is still wrong, the range is the problem:
- Select the whole table, not one column, so adjacent rows stay together.
- Remove blank rows inside the data. Excel may stop at the first blank row.
- Unmerge cells. Merged cells cannot be sorted reliably.
- Clear filters first. Hidden rows can be left out of a sort.
- When Excel asks, choose Expand the selection.
- Use Data → Sort and set the date column as the primary key, rather than the A→Z button on a mixed selection.
For a PivotTable, convert the source dates and click Refresh. Text dates appear as individual labels and cannot be grouped by month or year.
Prevent the Problem Next Time
- Format the column as Date before typing or pasting.
- Import CSV files through Power Query and set the locale per column.
- Keep sortable dates as real dates; use TEXT only for display labels.
- Add data validation so a text date is rejected at entry.
- Keep a helper ISO column (
=TEXT(A2,"yyyy-mm-dd")) only for reports, not for the column you sort.
Before You Submit
Sort the date column oldest to newest, then check the first and last row against the source. Filter by month and confirm the counts look sensible. A date that is one day off is often a locale problem, not a formula problem.
Use the Date Fixer for pasted dates, the Date Calculator for differences and workdays, and the Workbook Health Check for the formulas that depend on those dates.
Related: SUMIFS between two dates, formula not calculating, Power Query data cleaning, and the Excel troubleshooting hub.
