When Excel dates refuse to reformat, convert text to real dates, check regional settings, and apply the right number format.
Why Your Excel Date Format Won’t Change
Excel stores valid dates as numbers. If a cell holds plain text, format changes won’t stick. Imported CSV data, copy-pasted reports, or entries with stray spaces often land as text.
Mixed systems also bite. A workbook can use the 1900 system or the 1904 system; shifting between them moves dates by years. Another trap is a leading apostrophe, which forces text.
Start with a quick test. Select a date cell and set the format to General. If you see a whole number like 45123, Excel sees a true date. If it stays as 12/07/2025 or similar, it’s text.
Fast Clues And Fixes
| Symptom | Likely Cause | One-Step Fix |
|---|---|---|
| Format won’t change at all | Text value or leading apostrophe | Remove apostrophe; convert to date |
| Dates jump by 4 years | 1904 date system enabled | Turn off 1904 system in options |
| Only some cells change | Mixed types in the range | Fix text cells, then format |
| ### shows in cells | Column too narrow | Widen column |
| Strange month/day swap | Locale mismatch | Match regional settings |
| Formula returns #VALUE! | DATEVALUE can’t read text | Repair text or parse parts |
Once you know what you’re facing, choose the right path below.
Make Excel Recognize Dates (Then Format Them)
Method 1: Text To Columns
Select the problematic column. Go to Data → Text to Columns. Pick Delimited, uncheck all delimiters, choose Date and the correct order like DMY or MDY, then Finish. This converts text to real dates without formulas.
Method 2: DATEVALUE Or VALUE
If your dates look like 14/07/2025 or 2025-07-14 as text, use formulas in a helper column. =DATEVALUE(A2) turns a readable text date into a serial number. =VALUE(A2) can do the same when time is present. Copy the results, paste as values, then apply a date format you prefer.
When DATEVALUE Fails
DATEVALUE follows your system’s short date order. If the text doesn’t match that order, the result can be an error. Fix by changing the order or by rebuilding the date from parts, like DATE(YEAR, MONTH, DAY).
Method 3: Rebuild From Parts
When the text is messy, split it. Use LEFT, MID, RIGHT, or TEXTSPLIT to grab year, month, and day. Then use =DATE(Y,M,D) and add any time with +TIME(H,M,S).
Method 4: Power Query For Imports
Load the table to Power Query. Set the column type to Date or Date/Time. Add locale during conversion if needed. Load back to the sheet and format once. This keeps future refreshes clean.
- Trim spaces with =TRIM or clean hidden characters with =CLEAN.
- Replace dots or dashes with slashes using Find and Replace.
- Remove a leading apostrophe by editing the cell or running Text to Columns.
- Clear conflicting formats: Home → Clear → Clear Formats, then format again.
Apply A Date Format That Sticks
After conversion, pick Home → Number group → Short Date or Long Date, or open Format Cells for custom codes like dd-mmm-yyyy or yyyy-mm-dd. Custom codes change only the display; the value stays numeric.
When sharing across regions, ISO 8601 style yyyy-mm-dd avoids confusion. You can also select a locale in Format Cells → Date to lock the display to a language again.
Excel Date Not Changing Format Fixes In Real Work
You inherit a sheet with 01.02.23 which could mean February 1 or January 2. Use Text to Columns with the right order to force the meaning you want. Then apply a clear display like 01-Feb-2023 so no one misreads it again.
Another case: a Mac file opens on Windows and every date moves by 1,462 days. Switch the workbook off the 1904 system and the values fall back in line.
One more: a CSV loads dates as yyyy-mm-dd hh:mm and Excel keeps them as text. VALUE handles these strings well; once converted, apply a custom format like dd/mm/yyyy hh:mm to match your report.
Regional Settings And Locale Pitfalls
Excel follows the device’s regional settings. A formula that expects mm/dd/yyyy on one laptop can fail on another set to dd/mm/yyyy. Use locale-aware conversions or store clean serial dates and format for readers.
Locale Examples That Break Formatting
| Locale | Text In Cell | What Excel Might Read |
|---|---|---|
| United States | 03/07/2025 | March 7, 2025 |
| United Kingdom | 03/07/2025 | 3 July 2025 |
| ISO style | 2025-07-03 | July 3, 2025 when recognized |
If a workbook lives in the cloud, set the regional format for the file or the site so the team sees the same result.
Stop The 1900 vs 1904 Date Shift
Windows defaults to 1900; older Mac sheets may use 1904. A workbook set to 1904 shows each date offset by 1,462 days compared with 1900. You can toggle this per file in options. If numbers were re-entered while the wrong system was on, converting back may require adding or subtracting 1,462.
To check the raw number behind a date, set the cell to General. You’ll see the serial value that drives calculations and formatting.
Stubborn Cases And Safe Workflows
- Paste Special → Values to freeze converted dates before deleting helper columns.
- Use Data Validation to force date entry and block stray text.
- Avoid merged cells in date columns; keep one value per cell.
- When importing, set column data type to Date at the source step.
- Keep an ISO display in a spare column when sending files to mixed regions.
Quick Troubleshoot Checklist
- Set a suspect cell to General. Number means true date; unchanged string means text.
- Check for a leading apostrophe by editing the cell.
- Try Text to Columns with the right date order.
- Use DATEVALUE or VALUE in a helper column; paste results as values.
- Review regional settings and workbook locale.
- Verify the workbook’s date system; turn off 1904 if dates shifted.
- Apply a clear format such as dd-mmm-yyyy or ISO yyyy-mm-dd.
- Resize columns that show ### so the date can display.
Final Checks Before You Share
Scan a sample of converted dates with a quick MIN and MAX to confirm range. Sort ascending and spot oddballs. Then save, test on a second machine, and ship the workbook with clarity and confidence.
Method 6: Detect Text Versus Dates With Formulas
Use =ISTEXT(A2) and =ISNUMBER(A2) to scan a column. A Boolean sweep tells you which rows need conversion before you change formats.
Method 7: Paste Special Math Nudge
Insert 0 in an empty cell, copy it, select the text dates, use Paste Special → Add. When the text is clean, this math nudge pushes Excel to recalc the values as numbers.
Method 8: Multiply By 1 In A Formula
=A2*1 is a quick way to coerce a clean text date into a number. Copy down, paste values, then format. This works when the string already follows a standard pattern.
Custom Date Format Codes That Actually Display
Open Format Cells → Custom and type codes. Common picks: dd-mmm-yyyy, m/d/yyyy, dddd, mmm-yy, yyyy-mm-dd hh:mm. Add brackets for elapsed time like [h]:mm. The numeric value never changes; only the mask does.
If a code displays the wrong month or day names, pick a different locale in the same dialog. This locks the language of month and day labels, which helps cross-border reports.
Dates With Times: Hidden Fractions Matter
Excel stores time as a fraction of a day. 0.5 means noon. If you format a Date/Time as a Date only, the time still lives in the cell. Sorting or grouping can look odd when that hidden time differs.
To see the truth, switch to a format that shows both parts, or use =MOD(A2,1) to isolate the time fraction. Rounding can help when timestamps should snap to midnight.
Clean CSV Imports The Right Way
Use Data → Get Data → From Text/CSV. Preview the columns and set the type to Date or Date/Time. Pick the file origin and the correct locale so 12/07/2025 lands as the date you expect. Save the query for reuse.
If the file arrives with month names in a different language, choose Using Locale during conversion. That step lets Excel interpret names correctly before the data hits the sheet.
Windows, Mac, And Excel For The Web
On Windows, Region controls the short date order for the whole device. Excel follows that order. On Mac, check Language & Region. In Excel for the web, set File → Settings → Regional Format for the workbook when sharing.
Switching the workbook locale is safer than asking every user to change the device. It keeps the display consistent in OneDrive or SharePoint.
Avoid The TEXT Function Trap
=TEXT(A2,”dd-mmm-yyyy”) prints a date as text. It looks right, yet it won’t sort or reformat like a number. Use TEXT only for final display or export columns. Keep a numeric date alongside for math and pivot tables.
Template Tips That Prevent New Headaches
- Lock a clean input column with Data Validation set to Date.
- Provide a second column with ISO yyyy-mm-dd so readers can copy a universal value.
- Name the column headers clearly, like Date (serial) and Date (display).
- Save a sample file with Power Query steps so imports stay consistent.
- Add a small note near the header that the sheet uses the 1900 system.