
You select a column of dates, pick a new layout from the format list, click OK — and nothing happens. The dates look exactly as they did before, no matter which layout you choose. When excel date format not changing behaves like this, the format menu feels broken, and it is easy to assume the program is at fault. In most cases the cause is much simpler: the cells hold text that only looks like dates, and a number format can only restyle values Excel recognises as real dates. Until the stored value changes, no layout you pick can take effect.
This guide works through the causes in order, from checking what the cells really hold to the deeper fixes for imported data, regional settings, and damaged files. Nothing here deletes your data, and every fix can be tested on a copy first. Work through the fixes one at a time, and test the same cells after each one.
Table of Contents
- Why This Error Happens
- Quick Checks
- Diagnosing the Problem
- Fix 1: Check What the Cell Really Holds With the Formula Bar
- Fix 2: Re-Enter the Date After Setting the Cell Format to Date
- Fix 3: Use Text to Columns to Convert Text Dates to Real Dates
- Fix 4: Build a Custom Date Format in Format Cells
- Fix 5: Convert With DATEVALUE in a Helper Column
- Fix 6: Fix the Windows Regional Date Settings That Excel Follows
- Fix 7: Clean Hidden Characters With TRIM and CLEAN Before Converting
- Fix 8: Check for a Leading Apostrophe and Remove It
- Fix 9: Paste Values Into Fresh Cells Formatted as Date
- Fix 10: Copy the Data to a Fresh Workbook to Rule Out File Damage
- Fix 11: Repair Microsoft Office and Test in a New File
- What to Avoid
- Frequently Asked Questions
- Conclusion
- Helpful Official Resources
Why This Error Happens
A date layout in Excel is only a display choice: it changes how a stored value looks, not what the value is. The stored value has to be a real date — a serial number Excel counts from its start date — for any layout to apply. If the stored value is text, or if the layout itself is misread, or if the file is damaged, the display stays frozen. These are the reasons each of those causes happens most often.
1. The Cell Holds Text, Not a Real Date Value
Excel stores real dates as numbers and shows them through a layout. Text that looks like a date — typed with a leading apostrophe, pasted from a website, or imported from another program — is stored as plain characters, and no layout can restyle plain characters. The cell keeps showing the characters exactly as they were entered, whatever layout you pick. Text dates are the cause when the values sit left-aligned in their cells, when formulas ignore them, and when the layout menu changes nothing at all.
2. The Cell Was Formatted as Text Before the Date Was Typed
If a cell’s number format is Text before anything is typed into it, Excel stores whatever is typed as text, even if it looks like a perfect date. Changing the layout afterwards does not convert the stored text — the value has to be re-entered, or converted, before the layout can apply. This trap is common in columns copied from another sheet, where the Text format travelled with the copied cells. It is the cause when new dates typed into fresh cells format correctly, while the old column stays frozen.
3. The Import Problem Behind excel date format not changing
Data imported from a CSV file, a database, a website, or another program often arrives as text, because the import keeps the characters exactly as the source sent them. Imported dates can also arrive in a day-month order your system does not expect, so Excel refuses to read them as dates at all. The column looks like dates, sorts like text, and ignores every layout you apply. Imports are the cause when a whole column misbehaves at once, when the fault starts the day data was brought in from elsewhere, and when dates typed by hand in the same sheet work fine.
4. The Windows Regional Settings Use a Different Date Order
Excel reads typed dates using the date order set in Windows — day-month-year in the UK, month-day-year in the USA. A date typed in the other order can be misread, stored as text, or stored with the day and month swapped. Layouts marked with an asterisk in the format list follow the regional settings too, so they change their appearance when the regional settings change. Regional settings are the cause when day and month swap places, when some dates convert and others do not, and when a file behaves differently on two computers.
5. A Custom Format Code Is Misread
Custom layout codes use letters with strict meanings, and two of them are easy to confuse: m means month, except right after an hour code, where it means minutes. A code written for months in the wrong position shows minutes instead, and a code with a typing error shows the literal letters rather than the date. A column that is too narrow also hides the result, showing ##### instead of the date, which looks like a failed layout. Misread codes are the cause when the layout changes the display but shows the wrong parts, or shows hash marks instead of a date.
6. The Workbook or the Office Installation Is Damaged
A damaged workbook can hold corrupted format data that refuses every change, and a damaged Office installation can misbehave across every file. File damage is the least common cause, but it explains a layout fault that follows one file to every computer, or a fault that appears in every new file on one PC. The tests are simple: the same data in a fresh workbook, and a new file after an Office repair. If either test fixes the display, the damage was the cause.
Quick Checks
Two minutes before the deep fixes.
- Click a stubborn cell and read the formula bar — does it show a date, or the same characters as the cell?
- Check the alignment — do the stubborn values sit on the left of their cells, while working dates sit on the right?
- Check the Number Format box on the Home tab — does it say Text, General, or Date?
- Type a fresh date into an empty cell and change its layout — does the new date respond?
- Widen the column by double-clicking its right edge — do any
#####cells reveal their dates? - Note whether excel date format not changing affects one column, one sheet, or every file — that answer points at the data, the sheet, or the program.
Diagnosing the Problem
| Symptom | Cause | Go to Fix |
|---|---|---|
| Dates sit left-aligned, layout does nothing | Text, not real date values | Fix 1 |
| Layout works for new entries only | Cells pre-formatted as Text | Fix 2 |
| Whole imported column refuses to change | Imported text dates | Fix 3 |
| Layout changes the display but shows wrong parts | Wrong custom format code | Fix 4 |
| Day and month swap places | Regional date order mismatch | Fix 6 |
| Some cells convert, others do not | Hidden spaces or characters | Fix 7 |
| Apostrophe visible in the formula bar | Leading apostrophe forcing text | Fix 8 |
| Fault follows one file to every computer | Damaged workbook | Fix 10 |
Fix 1: Check What the Cell Really Holds With the Formula Bar
Start by finding out what Excel actually stored, because the cell display can mislead you. The formula bar shows the stored value exactly, and the alignment shows the type: real dates sit on the right, text sits on the left. This check takes seconds, and it tells you whether the fix is a conversion, a re-entry, or a layout repair.

- Click one stubborn cell and read the formula bar at the top.
- If the formula bar shows the same characters as the cell, the value is stored as text.
- If the formula bar shows a date in a standard order, the value is a real date and the layout is the fault.
- Click a working date cell and compare its alignment with the stubborn cells.
- Open the Number Format box on the Home tab and read the current format of the stubborn cells.
- Select the stubborn cells and set the format to General — real dates turn into serial numbers, text stays unchanged.
- Press Ctrl + Z to undo the General test, and note the result before moving on.
- The General test is the quickest proof available: a real date becomes a number like 45658, while text stays exactly as it was.
- Do not worry if the numbers look alarming — the undo step restores the display, and the test changes nothing permanently.
- If the values are text, move on to the conversion fixes, starting with Fix 2.
- If the values are real dates, skip to Fix 4, because the layout itself is the part that needs repair.
Fix 2: Re-Enter the Date After Setting the Cell Format to Date
When cells were formatted as Text before the dates were typed, the stored text has to be re-entered after the layout is fixed. Setting the layout first and then re-entering the value lets Excel read the entry as a real date this time. For a few cells, retyping is fastest. For a column, the edit-and-confirm trick re-enters every cell without retyping anything.
- Select the stubborn cells and open Format Cells with Ctrl + 1.
- In the Category list, click Date, choose a layout, and click OK.
- Click the first stubborn cell and press F2 to enter edit mode.
- Press Enter without changing anything — Excel re-reads the entry as a date.
- Check whether the cell now shows the layout you chose.
- Repeat the F2 and Enter steps down the column, or retype short columns by hand.
- Test the layout menu on the converted cells to confirm it now responds.
- The F2 and Enter trick works because editing a cell makes Excel parse its contents fresh, using the cell’s current format.
- It fails only when the text itself cannot be read as a date — hidden characters, a leading apostrophe, or an order the regional settings reject — and Fixes 6, 7, and 8 handle those.
- For a long column, Fix 3 converts the whole column at once, so use this fix for short ranges and Fix 3 for long ones.
- A cell that converts with this trick proves the value was fine and only the stored type was wrong.
Fix 3: Use Text to Columns to Convert Text Dates to Real Dates
Text to Columns is the fastest way to convert a whole column of text dates at once. It re-reads every cell in the column and stores the result as a real date, without changing the characters you see. The wizard asks a few questions, but for a plain column of dates the defaults are correct, and the conversion finishes in seconds.
- Select the whole column of stubborn dates, or the range that holds them.
- Click the Data tab, then Text to Columns in the Data Tools group.
- Keep Delimited selected and click Next.
- Clear every delimiter checkbox and click Next again.
- Under Column data format, choose Date, and pick the day-month order that matches your data.
- Keep the Destination as the original column and click Finish.
- Select the converted cells and apply the layout you want from the Number Format box.
- Choosing the right day-month order in step 5 matters: if your data is day-first and you choose month-first, days and months swap.
- If you are unsure, test on a copy of the column first and check a date whose day is above 12 — a swapped date is obvious there.
- Text dates also sort in the wrong order, because text sorts character by character, and our guide to Excel Sorting Data Incorrectly covers sort faults that share this cause.
- A column that converts cleanly here proves the values were text, and the layout menu will respond to them from now on.
Fix 4: Build a Custom Date Format in Format Cells
When the values are real dates but no built-in layout shows what you want, a custom layout code gives full control. The code is built from letters for day, month, year, hour, and minute, and the preview box shows the result before you apply it. Start from the closest built-in layout and edit its code, rather than writing a code from scratch.

- Select the cells and open Format Cells with Ctrl + 1.
- In the Category list, click Date and choose the layout closest to what you want.
- Click Custom in the Category list — the code for your chosen layout appears in the Type box.
- Edit the code using
dfor day,mfor month,yyyyfor the four-digit year, and separators such as/or-. - Read the Sample box to check the result on the active cell.
- Click OK and check the cells at full width — widen the column if any cell shows
#####. - If the display shows literal letters, reopen Format Cells and correct the code.
- Remember that
mmeans minutes when it follows an hour code, so a time layout needsh:mmwhile a date layout needsdd/mm/yyyy— mixing the two shows the wrong parts. - Layouts that begin with an asterisk follow the Windows regional settings and can change their appearance on another computer, while layouts without one stay fixed.
- Formatting faults often cluster together, and if rules that colour cells by their values also misbehave, our guide to Excel Conditional Formatting Not Working covers that side.
- A custom code that previews correctly and applies cleanly proves the values were real dates all along.
Fix 5: Convert With DATEVALUE in a Helper Column
The DATEVALUE function turns a text date into a real date value, and a helper column lets you convert without touching the original data. The function reads the text in one cell and returns the date it represents, which you can then format any way you like. Once the helper column is correct, paste its values back over the originals.
- Insert a blank column next to the stubborn dates.
- In the first cell of the new column, type
=DATEVALUE(A2), using the address of the first stubborn cell. - Press Enter and check whether the result shows a date or a serial number.
- If the result is a serial number, apply a date layout to the helper cell.
- Fill the formula down the whole column by double-clicking the fill handle.
- Select the helper column, copy it, and use Paste Special, then Values, over the original column.
- Delete the helper column and test the layout menu on the converted cells.
- If
DATEVALUEreturns an error, the text cannot be read as a date in your regional order — check Fix 6 for the order, and Fix 7 for hidden characters. - The helper-column method is the safest conversion available, because the originals stay untouched until the converted values are checked.
- Keep the helper column until you have checked several converted dates against the originals, especially dates whose day is above 12, where a swap would be obvious.
- A helper column that converts cleanly proves the text was readable and only the stored type was wrong.
Fix 6: Fix the Windows Regional Date Settings That Excel Follows
Excel reads typed dates using the Windows regional settings, so a mismatch between the data’s order and the system’s order blocks conversion. Changing the regional short-date order to match your data lets Excel read the dates correctly, and layouts marked with an asterisk follow the same settings. This fix changes a Windows setting, so note the current value before you change it.
- Open Settings, then Time & language, then Language & region.
- Click Regional format and read the current region and date order.
- Open Control Panel, then Clock and Region, then Region.
- On the Formats tab, check the Short date pattern and set it to match your data’s order.
- Click Apply, then OK, and close both windows.
- Return to Excel, select the stubborn cells, and try the conversion in Fix 3 again.
- If the order is now correct, test a fresh typed date to confirm Excel reads it properly.
- Change the regional settings back if the new order breaks other programs — the setting is system-wide, and some programs depend on it.
- A safer alternative for a single file is to choose the matching order inside the Text to Columns wizard in Fix 3, which converts without changing Windows at all.
- Regional settings are also why a file can behave on one computer and misbehave on another: the file is the same, but the system reading it is different.
- A conversion that succeeds after this fix proves the order, not the data, was the barrier.
Fix 7: Clean Hidden Characters With TRIM and CLEAN Before Converting
Imported and pasted dates often carry invisible extras: leading spaces, trailing spaces, non-breaking spaces, and control characters that stop Excel reading the value as a date. The TRIM function removes extra spaces, and the CLEAN function removes non-printing characters. Used together in a helper column, they strip the extras and leave a clean date the conversion fixes can read.

- Insert a blank column next to the stubborn dates.
- In the first cell of the new column, type
=TRIM(CLEAN(A2)), using the address of the first stubborn cell. - Press Enter and check whether the result looks cleaner than the original.
- Fill the formula down the whole column by double-clicking the fill handle.
- Copy the helper column and use Paste Special, then Values, over the original column.
- Run the Text to Columns conversion from Fix 3 on the cleaned column.
- Apply the layout you want and confirm the menu now responds.
TRIMdoes not remove non-breaking spaces, which websites and some programs insert — if a cell still resists after cleaning, use Find and Replace to replace the non-breaking space character, or useSUBSTITUTEto remove it by its code.- Dirty data also breaks filters and lookups, not just layouts, and our guide to Excel Filter Dropdown Not Working covers filter faults that share this cause.
- A column that converts after cleaning proves the dates were fine and only the invisible extras were in the way.
Fix 8: Check for a Leading Apostrophe and Remove It
A leading apostrophe tells Excel to store the entry as text, whatever it looks like. The apostrophe is invisible in the cell but visible in the formula bar, which is why this cause hides so well. Removing the apostrophe and re-entering the value lets Excel store a real date. For a whole column, Find and Replace or the Text to Columns wizard removes the effect at once.
- Click a stubborn cell and read the formula bar for a leading apostrophe.
- If the apostrophe is there, click into the formula bar, delete it, and press Enter.
- Check whether the cell now responds to the layout menu.
- For a whole column, select it and run the Text to Columns conversion from Fix 3.
- Alternatively, select the column and use Find and Replace to remove the apostrophe character.
- Apply the layout you want to the converted cells.
- Check several cells in the formula bar to confirm no apostrophe remains.
- Do not confuse the apostrophe with a quote mark inside the data — the leading apostrophe sits at the very start of the entry and never shows in the cell itself.
- Data pasted from some programs carries an apostrophe on every value, which is why a whole column can misbehave at once.
- A cell that converts the moment the apostrophe is gone proves the character, not the date, was the fault.
- From now on, type dates without any leading character, and let the cell format decide how they look.
Fix 9: Paste Values Into Fresh Cells Formatted as Date
When a range carries a mix of formats, stray formatting, or corrupted format data, pasting the values into fresh cells gives them a clean start. Fresh cells take the default General format, which reads dates correctly, and the layout you apply afterwards sticks. This fix keeps the values and drops everything else the old cells carried.
- Select the stubborn range and copy it with Ctrl + C.
- Click an empty area of the sheet, or a new sheet, and open Paste Special.
- Choose Values to paste only the stored values, without formats or formulas.
- Select the pasted range and set its format to Date from the Number Format box.
- If the pasted values are still text, run the Text to Columns conversion from Fix 3 on them.
- Apply the layout you want and confirm the menu responds.
- Copy the clean range back over the originals, or keep working in the new location.
- Paste Special with Values is the key step: a normal paste carries the old formats along, including a Text format that would restart the fault.
- If the pasted values show as serial numbers, that is good news — it proves they are real dates, and applying a date layout will display them properly.
- A range that behaves in fresh cells but not in the old ones proves the old cells’ formatting, not the values, was the fault.
- Keep the original range until the clean copy is checked, so nothing is lost if a value pastes wrongly.
Fix 10: Copy the Data to a Fresh Workbook to Rule Out File Damage
A damaged workbook can hold corrupted format data that refuses every change, even when the values themselves are fine. Copying the data to a fresh workbook tests this in minutes: if the layout menu responds in the new file, the old file is damaged, and the new file becomes your working copy. Your original file stays untouched during the test, so nothing is lost.

- Save a copy of the workbook under a new name, and work on the copy.
- Open a new blank workbook.
- In the copy, select the sheet’s used range and copy it with Ctrl + C.
- In the new workbook, use Paste Special, then Values, to paste only the values.
- Apply a date layout to the pasted dates and check whether the menu responds.
- If it responds, rebuild any formulas and formatting the sheet needs in the new file.
- Keep the original file as a backup until the new file has been used for a full day.
- If the new workbook also refuses the layout, the fault is in the values or the program, not the file — revisit Fixes 3 and 6, then Fix 11.
- File damage can also stop a workbook opening at all, and our guide to Excel File Not Opening covers file faults that go further than formatting.
- A fresh workbook that responds to the layout menu is the proof that the old file’s stored data, not your dates, was the fault.
Fix 11: Repair Microsoft Office and Test in a New File
An Office repair is the last resort, for a layout fault that appears in every new file on one PC. The repair replaces damaged program files while keeping your documents and settings, and it fixes faults that survive every file-level fix. Run the quick repair first, and the online repair only if the quick one does not help.
- Close Excel and every other Office program.
- Open Settings, then Apps, then Installed apps.
- Find Microsoft 365 or Microsoft Office in the list and click its three dots.
- Choose Modify, then choose Quick Repair, and click Repair.
- Wait for the repair to finish, then restart the PC.
- Open Excel, create a new blank workbook, and type a test date.
- Change the test date’s layout and confirm the menu responds.
- If the quick repair does not help, repeat the steps and choose Online Repair, which downloads fresh files and takes longer — keep the PC connected to the internet and plugged in.
- Do not use a downloaded “Office fixer” from an unknown website, and do not use pirated copies of Office, because those can damage the installation further.
- A new file that responds to the layout menu after a repair proves the program files, not your data, were the fault.
- If the fault survives an online repair, the cause is almost certainly a Windows regional setting or a damaged user profile, and Fixes 6 and 10 are the ones to revisit with fresh eyes.
What to Avoid
- Do not keep picking layouts on text values — convert the stored text to real dates first, or no layout can apply.
- Do not type dates with a leading apostrophe — it forces the entry to stay text, whatever layout you pick later.
- Do not retype a long column by hand — use Text to Columns or a helper column, which convert the whole range at once.
- Do not change the Windows regional settings without noting the old value — the setting is system-wide and other programs depend on it.
- Do not paste with a normal paste into fresh cells — it carries the old Text format along; use Paste Special with Values.
- Do not download an “Excel fixer” or a date-converter tool from an unknown website — use Excel’s own features and the steps in this guide.
- Do not use pirated software, activators, or cracks — they are a common source of the damage that breaks Office features.
Frequently Asked Questions
Why does the layout change for new dates but not old ones?
When excel date format not changing affects old entries while new ones respond, the old cells were formatted as Text before the dates were typed, so the values were stored as text. New cells use the default format and store real dates. Convert the old column with Text to Columns in Fix 3, or re-enter the values after setting the layout, as Fix 2 describes, and the menu will respond to them too.
Why do my dates show as numbers like 45658?
Because Excel stores real dates as serial numbers counted from its start date, and the General format shows the stored number instead of a date layout. The number proves the value is a real date, which is good news. Apply any date layout from the Number Format box and the number displays as a date again. Text dates never show as serial numbers, so this display is a useful test.
What is the difference between a text date and a real date?
A real date is a number Excel understands, so layouts restyle it, formulas calculate with it, and sorting orders it by time. A text date is plain characters that only look like a date, so layouts ignore it, formulas skip it, and sorting orders it alphabetically. The formula bar and the alignment tell them apart: real dates sit on the right, text sits on the left. Fix 1 shows both checks.
Why do day and month swap when I change the layout?
Because the data’s day-month order and the Windows regional order disagree. A date written day-first, read by a month-first system, stores with the parts swapped — or refuses to convert at all. Choose the order that matches your data inside the Text to Columns wizard in Fix 3, or set the regional short-date pattern to match in Fix 6, and check a date whose day is above 12 to confirm the order.
Will converting text dates change the actual dates?
No, if the conversion reads the order correctly. Conversion changes how the value is stored — from characters to a real date number — without changing the day, month, or year it represents. Check several converted dates against the originals, especially dates whose day is above 12, where a swap would be obvious. Keep a copy of the file until the converted column is checked.
How do I stop imported dates arriving as text?
Set the column format to Date before pasting, use Text to Columns with the matching day-month order right after importing, and use Get Data with the correct column type when importing from a file. Check the first few imported dates in the formula bar before working further. Catching text dates at import time takes seconds, while fixing a column later takes far longer.
Conclusion
When excel date format not changing freezes a column no matter which layout you pick, the fix is almost always a conversion, not a layout: the cells hold text, and text ignores every layout until it becomes a real date. Check the formula bar first, convert with Text to Columns or a helper column, clean hidden characters and apostrophes, match the regional order, and finish with a custom layout once the values are real. Test the same cells after every fix, because a column that responds to the layout menu is the only proof that counts. Convert once, format once, and the dates will display the way you choose.
