Excel Date Format Won’t Change? Causes and Fixes Guide

Excel Date Format Won't Change? Causes and Fixes Guide

If Excel date format won’t change, the cell may contain text instead of a real date, or a conditional format or protection setting may be affecting it. Select the cell and choose Home > Number Format > Short Date; if the display still does not change, check what the cell contains and convert text dates before applying a date format.

How Excel date formatting works

Excel stores most dates as numbers, then uses a number format to display those numbers as dates. For example, a cell can contain the numeric value for January 1, 2024, while its format determines whether it appears as 1/1/2024, 01-Jan-24, or Monday, January 1, 2024. Changing the format changes the appearance, not the underlying value.

This distinction explains the most common cause of the problem: formatting works on a true Excel date, but it cannot turn ordinary text that looks like a date into a date value. A cell containing the text “March 8, 2024” may look convincing, yet selecting Short Date will not necessarily change how that text appears. Excel needs to recognize or convert the entry first.

To apply a format in Excel for Microsoft 365 on Windows, select the cells, then use Home > Number Format and choose Short Date or Long Date. For more control, press Ctrl+1 to open Format Cells, select the Number tab, and choose Date or Custom. On a Mac, Command+1 opens Format Cells; the ribbon’s number-format controls are also available. Excel for the web has number-format choices on the Home tab, though its formatting dialog may offer fewer options than the desktop app.

First check whether the cell contains a number or text

Before trying several formats, identify the cell’s contents. Select it and look at the formula bar. A date entered as a recognized date usually appears there as a date value, while text may show a leading apostrophe or retain its exact original characters. The formula bar is useful, but alignment and appearance alone are not proof: cells can be aligned or styled in ways that conceal their type.

A quick test is to change the cell temporarily to General using Home > Number Format > General. A true date commonly becomes a serial number, such as 45292 for January 1, 2024, in a workbook using the standard 1900 date system. Text that looks like a date remains text. Change the format back afterward if needed. Avoid interpreting every number as a date: Excel also stores ordinary quantities as numbers, so consider what the cell is meant to represent.

A serial number appears: Excel has a numeric value. Apply a date format, then check for conditional formatting if the visible result remains unexpected.
The original date-like characters remain: The entry may be text. Convert it to a real date before formatting.
The cell shows something like ####: The value may be valid but the column may be too narrow. Widen the column or use a shorter date format.

Convert text dates into real Excel dates

Text dates often come from copied websites, CSV files, reports, or systems that export dates in a different regional order. For example, “2024-03-08” may be text even though it resembles a standard date. A leading apostrophe, such as ‘3/8/2024, also tells Excel to treat the entry as text. Formatting alone does not remove that instruction.

For a column of consistently structured text dates, try Excel’s Text to Columns conversion. Make a copy of the data first, especially if the column contains mixed content. Select the relevant cells, choose Data > Text to Columns, proceed through the wizard, and on the final step set the column data format to Date. Select the order that matches the source, such as MDY, DMY, or YMD, then choose Finish. Check the results carefully: choosing the wrong order can silently turn a date into a different day or month.

For a single entry, you can also edit the cell and re-enter the date in a format Excel recognizes under your regional settings, then press Enter. If the source uses a different date order, do not simply retype ambiguous values without confirming their meaning. For example, 03/04/2025 could mean March 4 or April 3. Use a clear source convention, such as year-month-day, and verify how Excel parsed the result before changing its display.

Text to Columns and re-entry are not universal fixes. If a cell contains mixed text, extra spaces, a time zone label, or a nonstandard month name, Excel may not parse every row correctly. Work on a copy, convert a small sample first, and compare the converted dates with the original source.

Check formulas that return text

A formula can display something date-like while returning text rather than a numeric date. This often happens when a formula uses Excel’s TEXT function to create a display string. Applying a date format to that result will not alter the text, because the number format applies to numeric values, not to characters returned by a formula.

To diagnose this, select the cell and inspect the formula bar. If the formula is deliberately building a formatted label, its output is text by design; changing the cell format is not the right fix. If the result needs to behave as a date for sorting, date arithmetic, or charting, revise the calculation so it returns a date value rather than a text representation. The appropriate change depends on the formula and the data feeding it.

Also check whether the source cell is text. A formula that refers to a text date may continue to produce text or an error, depending on how it is written and whether Excel can interpret the source. Confirm that the original input is a real date before troubleshooting the formula’s display.

Look for conditional formatting and cell styles

Conditional formatting can apply a number format when a rule is true. That format may make a cell look different from the ordinary format selected in the Number tab. In Excel for Windows, choose Home > Conditional Formatting > Manage Rules and inspect rules applying to the selected cell or range. Check whether a rule includes a number format and whether its condition is currently met. Edit or remove the rule only if it is not needed; it may be intentionally highlighting dates.

Cell styles and copied formatting can also make a workbook confusing. A style may apply a number format along with font, fill, and borders, and pasted cells can bring formatting from another range. Select the affected cell and inspect Format Cells to see its active number format. If you want to remove formatting, Home > Clear > Clear Formats removes formats but leaves cell contents; use it cautiously because it also clears fonts, fills, borders, and other formatting.

Sheet protection is another possibility. When a worksheet is protected, Excel may prevent users from changing the format of locked cells. Look for the Unprotect Sheet command on the Review tab. If protection is enabled, you need permission and, where required, the password from the workbook owner. Do not assume a format command failed when Excel is actually blocking edits.

Fix width, custom formats, and regional settings

Sometimes the date is valid and formatted correctly, but the column is too narrow to show it. Excel may display hash marks (####) for a date that does not fit. Drag the boundary between column headings to widen the column, or double-click the boundary to AutoFit. A shorter format, such as 8-Mar-24, can also fit where a long weekday-and-month format cannot.

If a built-in format is not what you need, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, select Custom, and choose or enter an appropriate pattern. For example, d-mmm-yyyy displays 8-Mar-2024, while mmmm d, yyyy displays March 8, 2024. In date format codes, capitalization can matter: mm represents a month in a date context, while m can represent minutes in a time context. Check the sample preview in Format Cells before applying a custom pattern to a large range.

Regional settings affect how Excel interprets typed dates and which built-in formats appear. A value such as 04/05/2024 is ambiguous unless you know whether the source uses month/day/year or day/month/year. Display formats can also use regional conventions. Changing a cell’s display to a different pattern does not correct a date that Excel already interpreted as the wrong day. For imports, confirm the source order during conversion and compare the result with an unambiguous reference.

When sorting, copying, or importing makes dates misbehave

Imported data is a frequent source of mixed date types: some rows may be true dates while others are text, perhaps because of blanks, inconsistent separators, or different source formats. A column can therefore appear nearly uniform but sort incorrectly. True dates sort in chronological order as numbers; text dates may sort alphabetically or behave inconsistently. Convert the whole column using a method appropriate to its structure, then sort a copy or test a small range to confirm the order.

Copying cells between workbooks can also produce unexpected results if the destination has different formatting or date-system settings. Excel workbooks can use the 1900 or 1904 date system; the systems differ by 1,462 days. This is uncommon in ordinary work, but it can matter when moving raw date serials between certain workbooks or exchanging data with older files. If copied dates shift by years rather than merely changing their display, check the workbook’s date-system setting and the source of the values instead of repeatedly changing the number format.

For a safer transfer, copy a small sample first and check both the formula bar and the displayed date in the destination workbook. If the source is an external file, inspect its import options and specify the date order where available. Retain an untouched copy of the original data until you have confirmed that dates, times, and sorting all behave as intended.

A reliable troubleshooting sequence

Follow these checks in order so you do not convert or overwrite data unnecessarily:

Select one affected cell and apply a built-in Short Date format from Home > Number Format.
If it does not change, set the cell to General and see whether it reveals a serial number or stays as text.
If it is text, identify the source’s date order and convert a sample using Data > Text to Columns, or re-enter a single unambiguous date.
If it is a number but still looks wrong, inspect conditional formatting, the active number format, column width, and sheet protection.
Check the formula bar and the surrounding rows for formula-generated text or a mixture of text and real dates.
Verify a few converted values against the source before applying the fix to the full dataset.

This sequence separates two different jobs: converting the stored value and choosing how to display it. Once Excel holds a genuine date value, changing its number format should affect its appearance without changing the date itself.

Frequently Asked Questions

Why does Excel show a number instead of a date?
If a date-formatted cell is changed to General, Excel displays its underlying serial number. Apply Short Date or another date format to show it as a calendar date. If the number is not a plausible date serial, check whether the cell contains a different numeric value.

Why does Excel show #### instead of a date?
The column is often too narrow to display the formatted date. Widen it or choose a shorter date format. If widening does not help, check that the cell contains a valid value and that the worksheet is not using an unusual custom format.

Why does changing the format not convert a text date?
Number formats change how numeric values appear; they do not reliably transform text into numbers. Convert the text using Text to Columns with the correct date order, or re-enter the value in a format Excel recognizes.

How can I tell whether an Excel date is text?
Change the cell temporarily to General and inspect the formula bar. A true date normally becomes a serial number, while text generally stays as date-like characters. Because formatting and alignment can be customized, do not rely on appearance alone.

Why does my date change to the wrong month or day?
Excel may have interpreted an ambiguous entry using a different regional order than the source. Confirm whether the original means MDY, DMY, or YMD, then convert it using that order. A new display format cannot repair a date that was parsed incorrectly.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.