Excel Date Format Not Changing? Fix Text Dates, DMY/MDY Mix-Ups, and Serial Numbers

If you changed a cell's format to Date and nothing happened, you are almost certainly dealing with one of three different problems — and each one needs a different fix.

Pick your symptom first

1. Changing the format does nothing at all. The cell still shows exactly what you typed, for example 01/02/2026 or 2026-02-01. → Your value is text, not a real date. Go to Fix 1.
2. It changes, but to the wrong day and month. You expected 1 February and got 2 January, or the other way round. → Your value was parsed in the wrong day/month order (DMY vs MDY). Go to Fix 2.
3. It shows a number like 44562. → Your value is a real date already. Excel is just displaying it as a number. Go to Fix 3.

Quick test: is it a real date or text?

Click a problem cell and look at the formula bar. Then, in a blank cell, type (replacing A2 with your problem cell):

=ISNUMBER(A2)

• TRUE means Excel has stored a real date (or a real number). Your problem is display, not conversion — go to Fix 2 or Fix 3.
• FALSE means Excel has stored text. No format change will ever turn text into a date. You need Fix 1.

If half your column returns TRUE and half returns FALSE, you have a mixed column: fix the FALSE rows, don't reformat the whole column and hope.

Fix 1: text dates — use Text to Columns

For Excel on desktop:

1. Select the column (or range) containing the text dates.
2. Go to Data → Text to Columns.
3. Choose Delimited, click Next, click Next again (you do not need a delimiter for this fix).
4. Under “Column data format”, choose Date.
5. In the dropdown next to Date, choose the order that matches the source data, not your computer's setting: DMY if the source wrote day first, MDY if the source wrote month first.
6. Click Finish.
7. Now apply your Date format (Format Cells → Date, or the Number Format dropdown). This time it should work, because the cells now contain real dates.

Why step 5 matters. Take the string 01/02/2026: if the source was UK-style (day first), the correct result is 1 February 2026; if it was US-style (month first), the correct result is 2 January 2026. Same string, two different correct answers. Excel cannot know which you meant — that is why the DMY/MDY choice is the whole fix.

Single-cell alternative. For one cell, or inside a formula, you can use =DATEVALUE(A2). DATEVALUE converts a text date into a real date, but it reads the text using your locale's date pattern, so it inherits the same DMY/MDY problem. If DATEVALUE returns an error or the wrong day/month, the pattern mismatch is the cause, and Text to Columns with an explicit DMY/MDY choice is the more reliable fix.

Google Sheets note: Sheets does not have an identical Text-to-Columns Date step, so do not present these Excel steps as if they work unchanged in Sheets. This guide is Excel-first.

Fix 2: the DMY/MDY mix-up

If your dates parsed, but to the wrong day and month, the conversion already happened — with the wrong order. The fix is to re-convert with the right order, not to reformat. The cleanest way is to go back to the original text (re-import it, or paste it into a blank column as text) and run Fix 1 with the other DMY/MDY choice.

A date like 13/02/2026 can only be day-first (there is no 13th month), so it parses unambiguously, while 01/02/2026 is genuinely ambiguous. Check ambiguous rows against a source you trust (an invoice, an email, a calendar entry) rather than assuming.

ISO dates. A date written as 2026-02-01 (year-month-day) is unambiguous: year first, then month, then day. If your ISO text dates are not converting, the problem is usually that they are text (Fix 1), not that the order is ambiguous.

Fix 3: serial numbers — it is already a date

Excel stores dates as serial numbers: in the default 1900 date system, 1 represents 1 January 1900, and each day after that adds 1. So a cell showing a plain number instead of a date is not broken — it is a real date displayed with a General or Number format instead of a Date format.

Fix: select the cells, then Format Cells → Date (or the Number Format dropdown → Short Date / Long Date). The number becomes a readable date, and — importantly — it stays calculable: you can subtract it from another date, add days to it, and use it in formulas.

Display-only vs keep-calculable — do not mix these up

• Format Cells → Date changes the display and keeps a real, calculable date. Use this when the date will be used in formulas, sorting, or filtering.
• TEXT() changes the value into text for display, for example =TEXT(A2,"dd mmm yyyy"). That looks right, but the result is no longer a date: you cannot subtract it, sort it as a date, or use it in a date calculation without converting it back. Use TEXT() only for labels (a report heading, a filename, a sentence), never as a substitute for fixing a text date.

Prevention: stop it happening on the next import

• Format the destination column as Date before you paste or import, where your import method allows it.
• If you import regularly from the same source, use Power Query and set the column type explicitly in the import step, so the DMY/MDY choice is made once and reused.
• Never format a column as Text and then type dates into it. That is the most common way to create the “format does nothing” symptom: the cell was Text before the date was entered, so Excel stored text and will keep storing text until you convert it.

If your symptom is different: if your dates are also dirty in other ways (spaces, invisible characters), clean the pasted text first with the Data Cleaner. If formulas are ignoring your dates, see numbers stored as text.

← Back to home