If Excel is not formatting a date, first check whether the cell contains a real Excel date or text that only looks like one. Select the cell and use Home > Number Format to choose a date format; if its appearance does not change, convert the text value into a real date before formatting it.
Quick checks to find the cause
Date problems usually come from either the cell’s underlying value or the way Excel is displaying it. A quick sequence of checks can distinguish those issues before you change a whole column:
- Select the cell and look at the formula bar. A date stored as text may appear with a leading apostrophe or remain left-aligned, although alignment alone is not proof.
- Choose Home > Number Format > Short Date. If a number changes into a date, it was already a numeric date value.
- If the value stays exactly the same, it may be text. Try converting it with Data > Text to Columns, or use a suitable formula for its known pattern.
- If you see a number such as 45750, Excel may already have a date serial; apply a date format rather than converting the number.
- If you see ####, widen the column first. The cell may be a valid date that does not fit in the available width.
Before applying a fix to many rows, test it on one cell. Dates imported from different countries can use different day-and-month orders, so converting a whole column with the wrong order can silently produce incorrect dates.
How Excel stores dates
Excel does not store an ordinary date as a collection of month, day, and year characters. It stores dates as serial numbers and uses the cell’s number format to display those numbers in a readable way. In the standard Windows date system, the serial number counts days from the start of 1900, with a historical compatibility quirk involving the year 1900. That is why a date-formatted cell can be changed to General and suddenly display a number.
Time is stored as a fraction of a day. For example, a value can represent a date plus a time even when the cell format displays only the date. Applying a format that includes hours and minutes may reveal that hidden time component. Conversely, formatting a value as a date does not create a date from arbitrary text; it only changes how a numeric value is shown.
Excel workbooks can use either the 1900 or 1904 date system. The systems number dates differently, with a 1,462-day difference for the same date. This matters mainly when moving or copying date values between older workbooks or workbooks configured with different systems. For most new Windows workbooks, the 1900 system is used. If dates shift by several years after a workbook transfer, check File > Options > Advanced > When calculating this workbook for the date-system setting; do not compensate by manually changing every date.
Tell a real date from text
Formatting works only when the cell contains a numeric value Excel can format as a date. Text such as 2025-04-03 may look unambiguous to a person but still be text, particularly if it came from a CSV, website, database, or another application. A leading apostrophe, such as '4/3/2025, deliberately tells Excel to keep the entry as text. The apostrophe is generally not displayed in the cell.
Useful clues include alignment, sorting, and arithmetic, but none should be treated as a definitive test by itself: users can change alignment, and mixed columns can contain both text and dates. Select a suspect cell, change its format to General, and inspect the result. A numeric serial suggests a real date value; a string that remains in a date-like pattern is likely text. You can also test a single cell with a formula. With a candidate value in A2, enter =ISNUMBER(A2) in another cell; TRUE means Excel stores the value as a number, while FALSE indicates it is not numeric. A TRUE result does not prove the number is a sensible date, so check the actual value too.
Format a date value that is already numeric
When the value is numeric, choose the appearance you want rather than trying to convert it. Select the cell or range, then use Home > Number Format and choose Short Date or Long Date. For more control, open the Format Cells dialog with Ctrl+1 on Windows or Command+1 on Mac, choose the Number tab, and select Date or Custom. A custom pattern such as yyyy-mm-dd displays a year-month-day style date; formatting changes the display, not the underlying date value.
The same date can therefore appear as 4/3/2025, April 3, 2025, or 2025-04-03, depending on the chosen format and regional settings. Choose a format that makes the day and month clear when sharing a workbook internationally. If a cell remains unchanged after choosing a date format, return to the text-versus-number check; repeating the formatting command will not convert text.
Also inspect whether the cell is part of a table or has a style applied. A table style can affect colors and borders, while the number format controls how a numeric date appears. If a value looks different from nearby cells, select it and check the Number Format menu rather than assuming the table’s visual style is the cause.
Convert text dates into real dates
For a column of consistently structured text dates, Text to Columns is often a direct option. Make a copy of the data first if the original values must be preserved, then select the affected column and choose Data > Text to Columns. In the wizard, select Delimited and click Next, leave the delimiters unchecked unless the data actually uses them, and click Next again. Under Column data format, choose Date and select the order that matches the source, such as DMY or MDY. Click Finish, then apply the desired date display format.
The date-order choice is crucial. The text 03/04/2025 could mean March 4 or April 3. If the source uses day-month-year but the conversion is set to month-day-year, Excel may interpret the value incorrectly or fail to convert it. Confirm the source convention from the file or sender; do not guess from a few ambiguous dates.
For one text value that Excel recognizes in the computer’s regional settings, DATEVALUE can convert it. With April 3, 2025 in A2, enter =DATEVALUE(A2) in another cell. The result is a numeric date value for April 3, 2025; format the formula cell as a date to display it as a readable date. DATEVALUE depends on recognizable date text and regional conventions, so it may return an error or interpret an ambiguous string differently than intended.
If every value uses the fixed ISO-style pattern YYYY-MM-DD, a formula can extract the year, month, and day explicitly. With text 2025-04-03 in A2, enter =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)) in another cell. It returns a real date for April 3, 2025; apply a date format to the result. This approach assumes every value has that exact pattern and uses four digits for the year, two for the month, and two for the day. Check converted results before replacing the source column.
Dates from formulas, CSV files, and imports
A formula can return a date serial, text that resembles a date, or an error. A formula built with DATE generally returns a numeric date that can be formatted normally. By contrast, TEXT turns a value into text for display. That can be useful for a report label, but a TEXT result will not behave like a date in date arithmetic or chronological sorting. If a formula-generated date will be used in calculations, keep it numeric and set the cell’s number format instead of converting it to display text.
CSV files are a frequent source of trouble because a CSV stores characters, not Excel cell formats. When opening or importing one, Excel may infer types based on the computer’s regional settings, and ambiguous dates may be interpreted differently from the source. On Windows, use Data > From Text/CSV to preview an import when you need to control how columns are interpreted. Check the preview and confirm date order before loading data, especially when a file combines dates, identifiers, and numbers.
Importing can also alter values that resemble dates but are actually codes. For example, a product identifier such as 3-4 should not necessarily become a date. Set such a column to Text during import when preserving its exact characters matters. If the source column should be dates, verify a few known records after import and then apply a consistent display format.
Other display problems to check
If a date displays as ####, widen the column by dragging its right boundary or double-clicking that boundary to AutoFit. A date or date-and-time value may simply need more space. Negative date or time results can also display as #### under the usual date system; check the calculation and input values rather than treating the number signs as a formatting code.
If the date shows a serial number, select a date format. If it shows the formula itself instead of the result, check whether the cell is formatted as Text, whether the formula begins with an apostrophe, or whether Show Formulas is enabled. Change a formula cell from Text to General, then re-enter the formula; changing the format alone may not cause an already-entered text string to calculate.
When one cell ignores the format applied to neighboring cells, check for conditional formatting. A rule can apply a number format under certain conditions. Select the affected range and choose Home > Conditional Formatting > Manage Rules to inspect relevant rules. Also check for inconsistent cell formats copied from another workbook. If the source is text, however, conditional formatting is not the basic conversion fix: convert the value first.
Frequently Asked Questions
Why does Excel show a five-digit number instead of a date?
If the cell contains a date serial, it is displaying the underlying number because its format is General or another non-date format. Select the cell and choose a date format. If it is not a date serial, formatting it as a date may produce an unrelated-looking date, so verify the value before changing it.
Why does changing the format not change my date?
The entry is probably text rather than a numeric date. Confirm by switching the cell to General or testing whether it is numeric. Convert consistent text dates with Text to Columns or an appropriate formula, then format the converted values.
How do I change 03/04/2025 to April 3 rather than March 4?
First confirm whether the source means day-month-year or month-day-year. Use that order when converting with Text to Columns, or parse a consistent, documented pattern with a formula. A display format cannot resolve an ambiguous text date after the wrong interpretation has already been made.
Why are dates showing as ####?
Widen the column; the value may not fit. If widening does not help, check whether the calculation produces a negative date or time value, which can be unsupported for display with the workbook’s date system.
Can I format dates in a CSV file?
A CSV does not preserve Excel number formats. Import it into Excel, convert or verify the date column, and save the result in an Excel workbook format such as XLSX if you need to retain cell formatting.
Related
- Excel Data Model Measures: Add, Format, and Use One
- Excel Data Model: How to Append Tables with Power Query
- Excel Data Run Analysis: A Practical Runs Test
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Exact Formula in Excel Not Working? Fix ItIf an exact formula in Excel is not working, first check that the cell is not formatted as…
- Remove Double Spaces in Word and Fix Double Line SpacingTo remove repeated spaces between words in Microsoft Word, open Home > Replace, enter two spaces in Find…
- SUMPRODUCT Excel #DIV/0! Error: Causes and FixesYes—SUMPRODUCT returns #DIV/0! when its calculation divides a value by zero, or when an input cell already contains…
- Excel Page Break on Row: How to Set and Fix ItTo put a page break above a specific row in Excel for Microsoft 365 on Windows, select that…
- Excel Text Not Staying in Cell? Fix the OverflowIf text looks as though it is spilling out of an Excel cell, it usually has not moved:…
- Circular Reference Not Working in Excel? Fix ItA circular reference in Excel will not calculate as expected if iterative calculation is off, the formula does…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.