Date Format Won’t Change In Excel | Fix It Fast

If a sheet refuses to show dates the way you want, don’t wrestle the Format menu again and again. Fix the value, not just the look. This guide shows clear checks and quick repairs that work on Windows, Mac, and Excel for the web.

Excel Won’t Change Date Format: Quick Fixes

Spot The Root Cause First

Before changing styles, confirm what’s in the cells. Dates in Excel are numbers under the hood. If the cell holds text, a broken locale, or a different date system, formatting won’t stick.

Likely Cause	What You See	Fast Test
Text, not a date	Left-aligned, leading apostrophe, or error tag	Change format to General: true dates turn into numbers
Locale mismatch	12/07 shows as 12 July on one PC and Dec 7 on another	Type 31/01—if it flips or errors, locale rules differ
1904 date system	All dates off by four years	Check workbook option “Use 1904 date system”
Hidden characters	Looks right but won’t sort or filter by date	LEN vs. CLEAN/TRIM result differs
Text format applied	Format Cells shows Text	Switch to Date or General and re-enter one sample

Convert Text Dates To Real Dates

Many imports deliver dates as text. Convert them, then apply a format. Microsoft documents a reliable path with the DATEVALUE function and Paste Special. Read the steps in Convert dates stored as text.

Quick Options

• DATEVALUE or VALUE: Build a helper column, e.g., =DATEVALUE(A2) or =VALUE(A2). Copy, then Paste Special → Values.
• Text To Columns: Data → Text to Columns → choose Date and the matching order (D/M/Y or M/D/Y). No split needed; it can convert in place.
• Power Query: Data → Get Data → From Table/Range → set the column type to Date (or “Using Locale” when the pattern differs from your PC).

Clean Hidden Characters And Odd Digits

Text dates can hide stray spaces, non-breaking spaces, or digit shapes from other scripts. Clean first, then convert.

• Trim and clean: =DATEVALUE(TRIM(CLEAN(A2))).
• Swap separators: If the source uses dots or dashes, try SUBSTITUTE to swap to slashes, then convert.

Fix Locale Mismatches (dd/mm vs mm/dd)

Dates follow regional rules. A file built in one region may open with different rules in another, so 03/04 could mean April 3 or 3 April. Pick one path and stick with it.

• During import: In Power Query, set the column type Using Locale so Excel interprets 01/02/2025 with the source rules.
• With Text To Columns: Choose the date order that matches the text. This locks the interpretation during the conversion.
• As a display choice: After you have real dates, pick a display that cannot be misread, e.g., dd-mmm-yyyy.

Check The Workbook Date System

Excel supports two systems: 1900 and 1904. If a workbook uses 1904, every date shifts by about four years. Microsoft explains both systems in Date systems in Excel. In Windows Excel, see File → Options → Advanced → “Use 1904 date system” (per workbook). On Mac, see Excel → Preferences → Calculation.

Stop Format Locks

When a range is set to Text, new entries stay as text. Change the format before you enter dates.

• Switch format, then re-enter one sample: Set to General or Date, type a date, confirm it becomes a number in General.
• Clear leftover styles: Home → Clear → Clear Formats to remove custom styles that force text.
• Check Conditional Formatting: Rules that format text can make dates look stuck. Temporarily disable rules to test.

Apply A Date Format That Actually Changes

Once you hold real dates, styles will work. Pick a built-in date or create a custom one.

• Format Cells → Date: Choose a Short or Long pattern you like.
• Custom patterns: Use tokens such as yyyy-mm-dd, dd-mmm, or mmm d, yyyy.
• Stable display for mixed regions: Prefer dd-mmm-yyyy to avoid 01/02 confusion.

Repair Paths By Scenario

Pick the path that matches your sheet. Each path converts the value first, then applies the look.

Scenario	Best Tool	Sample Step
CSV imported as text	Power Query	Set type to Date using the source locale
Mixed dd/mm and mm/dd	Text To Columns	Choose DMY or MDY to lock the read
Dates with extra spaces	Formula	=DATEVALUE(TRIM(CLEAN(A2)))
Dashes or dots as separators	Formula	=DATEVALUE(SUBSTITUTE(A2,".","/"))
Cells set to Text	Clear Formats	Set to General, retype one entry
Four-year offset	Workbook setting	Turn off 1904 system
Month names in another language	Power Query	Set type with the right locale

Quick Recap Checklist

• Test with General. True dates show as numbers.
• Convert text dates with DATEVALUE, Text To Columns, or Power Query.
• Clean spaces and odd characters before conversion.
• Lock the intended day-month order during conversion.
• Verify the workbook’s 1904 setting.
• Only then pick the display you want.

Safe Ways To Enter Dates That Always Work

Type Clear ISO Dates

Typing 2025-09-23 leaves no room for confusion. Excel reads this cleanly in most locales and it sorts well as text during entry. Once you confirm the cell holds a number under General, pick the display you like.

Use Unambiguous Month Text

If a shared file crosses regions, type month names: 23 Sep 2025. You can later switch to a numeric look without changing the value.

Control The Import

When pulling data, take charge of conversion at the door. Power Query’s “Using Locale” option prevents mixed rules from slipping in, and it documents the choice so teammates can refresh with the same logic.

Troubleshooting Notes For Mac And The Web

On Mac, the 1904 setting is common in older files. If dates shift by years after copy-paste between Mac and Windows, compare the setting in both workbooks and align them. In Excel for the web, the site or profile locale can steer date reading and display. If teammates see different looks for the same workbook, set a clear display like dd-mmm-yyyy or agree on a shared locale during import.

Why Formatting Seems To “Do Nothing”

Formatting does not change the value, only the display. A text cell won’t turn into a date until you convert it. That’s why real fixes start with the value and end with the look.

Practical Examples You Can Try

Text With A Trailing Space

In B2, enter =DATEVALUE(TRIM(A2)). Copy down, then paste values over column A. Pick a display like dd-mmm.

DMY Text On A US PC

Select the column → Data → Text To Columns → pick Delimited → Next twice → Date → DMY → Finish. The values become true dates, ready for any style.

Fix A Whole Table With Power Query

Load the range to Power Query. Right-click the column header → Change Type → Using Locale → Date → pick the source language and region. Close & Load. The query records each step so refreshes stay consistent.

Keep Dates Stable Across Machines

• Store true dates, not text. Test with General.
• Pick an unambiguous display such as yyyy-mm-dd for shared files.
• Avoid typing two-digit years; use four digits to prevent rollover surprises.

When Nothing Works

If conversion fails, inspect a raw value. Click in the Formula Bar and arrow through the string. If you see weird spaces, unexpected characters, or a trailing period, repair with SUBSTITUTE, TRIM, CLEAN, or a short Power Query step. Once a single sample converts, scale the method to the whole column.

Common Traps That Look Like Dates

Leading Apostrophes

Apostrophes tell Excel, “treat this as text.” You’ll see them when data comes from a system that tries to preserve the exact string. Delete the apostrophe or use Text To Columns to convert at scale.

Month Names Mixed With Extra Text

Strings like “Due 23/09/2025” won’t convert as is. Use TEXTAFTER, TEXTBEFORE, or MID to isolate the date piece, then wrap with DATEVALUE. In older builds, a short FIND and MID combo works well.

Two-Digit Years

Windows carries a setting that controls how two-digit years roll to centuries. Different PCs can disagree. Enter four-digit years in shared files, or convert strings with a formula that adds the right century before calling DATE.

Times Stuck As Text

Clock values face the same hurdles. If 14:30 refuses to change style, treat it like a date text issue: clean the string, then convert with TIMEVALUE or split with Text To Columns and rebuild with TIME.

Make Formats Stick Across Teams

After conversion, set a display that travels well. Save a cell style named “Clear Date” that uses yyyy-mm-dd or dd-mmm-yyyy. Apply it to date columns in your template for shared workbooks.

Protect Data Types During Paste

Paste values only when dropping dates into a styled range. This avoids importing stray text formats. If you must paste all, run a quick audit: switch one cell to General and confirm you see a serial number, not a string.

PivotTables And Charts

Pivot grouping and date axes only work with true dates. If grouping by month fails, your source is text. Fix the column in the source table, refresh the Pivot, and the month, quarter, and year groups will appear.

Final Tip

Dates behave when the value is a true serial number and the workbook uses the right system and locale. Fix those first. The format you want will follow with one click.