Excel date format not changing? Why, and how to fix it

Check first: is it a date or text?

Excel stores 15 March 2026 as the number 46096, a serial number that counts 1 January 1900 as day 1. A date format such as dd mmm yyyy tells Excel how to display that number. Text has no number underneath, so Ctrl+1 can change the format all day and nothing on screen moves.

Signs you have text: the dates sit on the left of the cell, the status bar shows a Count but no Sum when you select them, and they sort A to Z by the first character. ISNUMBER settles it. In the CSV export above, every value in column B is text, even though they look like dates.

Cause 1: the cell was formatted as Text before you typed

If a column is formatted as Text (often from a template or an import), everything typed into it is stored as text, dates included. Changing the format to Date afterwards does not convert what is already there. Excel only re-reads a cell when you enter it again, which is why pressing F2 and then Enter on one cell, after changing its format, suddenly "fixes" it.

For a whole column, re-entering by hand is not practical. Text to Columns makes Excel re-read every cell in one go.

1. Select the date column (one column at a time), press Ctrl+1 and set it to General, so the converted values are not stored as text again.
2. Go to Data > Text to Columns.
3. Choose Delimited, click Next, clear every delimiter box and click Next again.
4. Under Column data format choose Date and pick the order the text is in, such as MDY or DMY.
5. Click Finish, then apply the date format you wanted with Ctrl+1.

Cause 2: text dates from an export

CSV files, web copy-paste and system exports often deliver dates as text. When the text is in an order your Excel understands, two minus signs (or DATEVALUE) convert it to the date number. The result may first show as a plain number like 46096; apply a date format and it becomes a date.

Use this in a helper column, then copy the results and use Paste Special > Values over the original column if you want to replace the text. Both "03/15/2026" and "2026-03-18" convert this way on a month-first system.

Cause 3: day and month in a different order

Excel reads text dates using your computer's regional settings. On a US system (month first), "15/03/2026" cannot be a date because there is no month 15, so DATEVALUE returns #VALUE! and the cell stays text. Worse, "03/04/2026" does convert, but as 4 March when the sender meant 3 April. Both problems come from a file made on a day-first system.

When the order does not match, rebuild the date from its parts with DATE, which works the same on every system. For yyyymmdd text such as "20260325", use =DATE(LEFT(B7,4),MID(B7,5,2),RIGHT(B7,2)). In Text to Columns, choosing DMY or YMD in step 3 does the same job without formulas.

Cause 4: hidden spaces

Data copied from web pages and some exports carries a non-breaking space (character 160) at the start or end. It is invisible, but Excel does not treat it as a space, so the value is not recognized as a date, and TRIM alone does not remove it. "03/20/2026" in B6 is 11 characters long instead of 10, and =LEN(B6) gives it away.

Swap the non-breaking spaces for normal spaces, trim, then convert. If the dates also have ordinary leading spaces or line breaks, this one formula covers those too.

Causes 5 and 6: TEXT formulas and regional formats

If the cell holds a formula like =TEXT(C7,"dd/mm/yyyy") or ="Due "&C7, its result is text, so the Format Cells setting is ignored. Change the format code inside the TEXT function instead, or point the cell at the date itself (=C7) and format that.

The other surprise is a format that changes by itself on another computer. In Format Cells > Date, the formats marked with an asterisk (*) follow the computer's regional date setting, so a file shows 22/03/2026 on a UK computer and 3/22/2026 on a US one. To make a date look the same everywhere, choose Custom and type a code such as dd-mmm-yyyy.