Excel Won’t Change Date Format | Fix It Fast

Why Date Format Changes Fail

Most trouble starts when the value is not a real date. It only looks like one. Excel stores true dates as serial numbers. If a cell holds pure text, changing the format does nothing. Other blockers include hidden spaces, non-breaking spaces from web pages, an apostrophe before the entry, mixed separators, or a region mismatch like month-day vs day-month. If the source is a CSV or export, the import step may have forced everything to text.

Run quick tests. Select a supposed date and change format to General. A true date shows a number. Text stays as the same string. Try =ISNUMBER(A2) and =ISTEXT(A2) on a helper column. These two checks tell you what you are dealing with before fixing anything.

Common Causes And Fast Fixes

Cause	How To Spot	Quick Fix
Text dates	General format still shows the same entry; green triangle may appear	Convert with Text to Columns, DATEVALUE, or paste special multiply by 1
Leading apostrophe	Formula bar begins with ‘	Remove with Find & Replace or VALUE
Hidden spaces / NBSP	TRIM works for spaces; non-breaking space needs CHAR(160) handling	CLEAN, TRIM, and SUBSTITUTE(A2,CHAR(160),” “) then convert
Wrong separators	Dots or dashes that do not match system settings	SUBSTITUTE to standard slash, then convert
Region mismatch	02/03 could be March 2 or 2 June, depending on locale	Use Text to Columns with explicit DMY or MDY
Imported as text	Data from CSV, XML, or systems list	Reimport with types, or fix using Power Query or conversion steps
Mac 1904 system	Dates off by about four years	Check workbook date system and switch if needed

Microsoft shows several reliable ways to turn text into real dates, including formulas and paste special conversion. See the guide on converting text dates for detailed steps.

Quick Checks Before You Start

Confirm the sheet calculation mode is automatic. Press Ctrl+1 to open Format Cells and review Number > Date. Try a known good cell with today’s date to prove the format works. If that one changes while problem cells do not, the issue is text or characters, not the format itself. Save a copy of the file before bulk changes so you can roll back if needed.

Check the cell format on the Home tab. If it shows Text, change it to General or Date before running any conversion. Use Find & Replace to strip stray apostrophes: replace a single quote with nothing. If the font color masks the content, Clear Formats, then apply a clean date format. Test on a small copy first.

Excel Not Changing Date Format: Step-By-Step Fixes

Method 1: Text To Columns

This wizard converts text to a typed column without formulas. Select the range, go to Data > Text to Columns. Pick Delimited or Fixed width. Click Next until Step 3. Select the date column in the preview. Set Column data format to Date and choose the matching order, such as DMY or MDY. Finish. The cells become real dates and any date format now works.

Method 2: DATEVALUE Or VALUE

Use a helper column. If A2 holds a text date like 31-01-2024, enter =DATEVALUE(SUBSTITUTE(A2,”-“,”/”)) and fill down. Then copy and paste values. Apply your target format. For numeric text like 20240131, build a date with =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)). For plain text that Excel already parses, =VALUE(A2) also works because dates are numbers underneath.

If DATEVALUE throws an error, the string may include extra characters. Wrap it: =DATEVALUE(TRIM(SUBSTITUTE(A2,CHAR(160),” “))). This replaces non-breaking spaces and trims regular spaces before conversion.

Method 3: Paste Special Conversion

Type 1 in an empty cell and copy it. Select the text dates and use Paste Special > Multiply. This forces a numeric cast. Switch format to your preferred date display. If that fails, try Paste Special > Add with a zero in the helper cell.

Fix Locale And Separator Mismatches

Dates rely on your system’s region. A file built on a different setting can misread month and day. Two fixes help. First, use Text to Columns and set the incoming order. Second, set a custom format code that includes a locale tag, such as [$-en-US]dd-mmm-yyyy or [$-en-GB]dd/mm/yyyy. The tag tells Excel which language to use for month names and ordering.

To change the display, open Format Cells > Number > Date and pick a type, or add your own code on the Custom tab. Microsoft’s article on formatting dates explains built-in options and custom codes.

Clean Hidden Characters

Web pages and PDFs often insert non-breaking spaces. You cannot see them, yet they block conversion. Combine CLEAN, TRIM, and SUBSTITUTE to sanitize before converting. Example: =DATEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),” “),CHAR(9),””))). The inner SUBSTITUTE swaps NBSP to spaces and drops tabs. TRIM removes leading and trailing spaces and reduces runs to single spaces. CLEAN deletes non-printable control codes.

If the separator itself is nonstandard, normalize it first. Replace periods with slashes using SUBSTITUTE, then convert. Example: =DATEVALUE(SUBSTITUTE(A2,”.”,”/”)).

Apply The Right Date Formatting

Once cells hold true dates, the format is only a display choice. Try short and long types, or custom codes. Useful codes include dd-mmm-yyyy for clear month names, mm/dd/yyyy for US style, dd/mm/yyyy for many other regions, and yyyy-mm-dd for sort-friendly strings. You can also show day names with ddd or dddd, and two digit years with yy.

Imported CSV And System Exports

CSV files carry plain text. During an open, Excel guesses types based on the content and your region. That guess can flip month and day or leave values as text. Instead of double-clicking the CSV, import it. Go to Data > Get Data > From Text/CSV. Choose the file. In the preview, set the file origin and delimiter, then use Transform Data to open Power Query and assign the Date type to each column. Load the result back to a table. The dates will arrive already typed.

Avoid opening by double-click; use the import path for control over types and locale.

For recurring feeds, save the query and refresh. The typing rules apply every time, so new rows come in as dates without manual cleanup.

Detect True Dates With Simple Tests

Use these quick checks on a helper column. =ISNUMBER(A2) returns TRUE for a real date. =ISTEXT(A2) flags text. If =A2+1 moves the day to the next day, the entry is numeric. If it throws an error or leaves the string unchanged, it is text. Another handy check is to format the cell as General. A real date shows a serial like 45567 while text shows no change.

Edge Cases That Trip People Up

Two Digit Years

Dates like 1/1/25 can map to 1925 or 2025 based on settings. Expand to a four digit year before conversion or choose a date order in the wizard. In custom formats, use yyyy to show the century.

1900 Vs 1904 Systems

Windows workbooks use 1900. Legacy Mac workbooks may use 1904. If imported values are four years off, check File > Options > Advanced > When calculating this workbook. Clear or set the 1904 date system to match the source, then recheck.

Non-Gregorian Calendars Or Local Scripts

Some exports write local month names or regional numerals. Text to Columns with the right locale tag helps, or Power Query with Locale during the type step. If the data uses a different calendar baseline, convert at the source or use a mapping table.

Mixed Columns

A single column may contain real dates and text. Sort by cell color from the green triangle or filter by =ISTEXT to target only the text rows. Fix those rows first, then set a uniform format for the whole column.

Power Query Clean-Up Route

Power Query is steady for messy feeds. Select your column, choose Data Type > Date. If the result shows errors, right-click the step and set Using Locale. Pick Date and the culture that matches the source, such as English United Kingdom. Add a Trim and Clean step from Transform > Format. You can also Replace Values to swap dots to slashes before the type step. Load to a table when preview looks correct.

Prevent The Problem Next Time

Type Dates, Not Strings

Enter dates with slashes or hyphens that match your region. Avoid month names with typos. If you see a left-aligned result, it is likely text. Reenter or convert at once so later steps are smooth.

Control Inputs

Turn the range into a table with Ctrl+T. Tables auto-fill formulas and formats down the column. Add a data validation date rule. The prompt helps users enter values correctly. If you receive files from partners, agree on a format like yyyy-mm-dd so imports behave the same on every system.

Pick Clear Formats

For reports, prefer month names or ISO style to avoid confusion. dd-mmm-yyyy and yyyy-mm-dd both read well across regions. For mixed audiences, add a note in the header that states the display style so nobody misreads a column.

Format Code	Shows	Use Case
dd-mmm-yyyy	05-Jan-2025	Readable month text to avoid ambiguity
yyyy-mm-dd	2025-01-05	Text export and natural sorting
[$-en-GB]dd/mm/yyyy	05/01/2025	Force day-month for UK style sheets
[$-en-US]mm/dd/yyyy	01/05/2025	Force month-day for US sheets
ddd, dd mmm	Sun, 05 Jan	Compact reports and headers
h:mm AM/PM	2:30 PM	Show time only when needed

Short Cheatsheet Of Formulas

Swap dots to slashes and convert: =DATEVALUE(SUBSTITUTE(A2,”.”,”/”)). Clean spaces then convert: =DATEVALUE(TRIM(SUBSTITUTE(A2,CHAR(160),” “))). Build from yyyymmdd text: =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)). Cast with math: =A2*1 or =–A2 when Excel can already parse the string. Convert with VALUE: =VALUE(A2). Always apply a date format after the value turns numeric.