Date Format in Excel: How to Fix It (Step by Step)

To fix a date that displays incorrectly in Excel for Microsoft 365 on Windows, select the date cells, press Ctrl+1, choose a date format on the Number tab, and select OK. If changing the format has no effect, the entries may be text rather than real Excel dates; convert them before applying a display format. These steps change how dates appear, not the underlying date value, unless you use a conversion method.

What You’ll Need

The Excel workbook containing the dates. If it contains important information, save a copy before converting or replacing values.
A clear idea of the intended order, such as day/month/year or month/day/year. For ambiguous dates such as 04/05/2026, confirm which part is the month and which is the day.
For text dates, the original date order or locale used to create or import the data. This helps Excel interpret the entries correctly.

Step-by-Step

Select the cells you want to fix. Click a cell, or drag across a range in the worksheet. To format an entire column, click its letter at the top. If you need to apply a consistent format to non-adjacent cells, hold Ctrl while selecting each range on Windows. Avoid selecting the whole sheet unless you intend to change every date-like value in it.
Open the Format Cells dialog. Press Ctrl+1 on Windows. Alternatively, right-click the selection and choose Format Cells. In Excel for Mac, select the cells and press Command+1, or use Format > Cells. In Excel for the web, select the range and use the number-format controls on the Home tab; available choices may be simpler than in desktop Excel.
Choose a date display format. In the Windows dialog, open the Number tab and select Date in the Category list. Review the examples in the Type list and choose one that matches the display you want, such as a short numeric date or a longer date with the month written out. The sample reflects the selected cell’s value and regional settings. Click OK to apply the format.
Set the locale if the date order is wrong. In the Format Cells dialog, use the Locale (location) list to select the region whose date conventions you need, then choose a matching Type. For example, a format with a four-digit year might display as month/day/year in one locale and day/month/year in another. Check the preview before accepting it. On Mac, the available locale and format controls can vary with the Excel and macOS versions; use the date formats shown in the dialog and check the preview.
Use a custom format when the built-in options do not match. In Format Cells, choose Custom and enter a pattern in the Type box. Common patterns include dd/mm/yyyy, mm/dd/yyyy, d mmm yyyy, and yyyy-mm-dd. In Excel date patterns, m represents the month, d the day, and y the year. Use mmm for a three-letter month name. A custom pattern changes the display; it does not rearrange the stored date or turn text into a date.
Check whether Excel recognizes the entries as dates. If the selected cells remain unchanged, display a serial number, or refuse to behave like dates, click one cell and inspect the formula bar. A real Excel date is stored as a number, even though it usually displays as a date. A date typed or imported as text may be left-aligned by default, but alignment alone is not proof: cell alignment can be changed manually. Try changing its number format to General. A recognized date typically shows a number; text remains text.
Convert consistently formatted text with Text to Columns. First, make a copy of the column. Select the text-date cells, then choose Data > Text to Columns. In the wizard, choose Delimited and click Next twice without selecting delimiters. Under Column data format, choose Date and select the order that matches the source, such as MDY or DMY. Click Finish. Then apply your preferred display format using the earlier steps. Confirm that the converted dates are correct, especially where day and month could be confused.
Handle mixed or ambiguous entries carefully. If a column contains several date patterns, do not convert the entire column using one assumed order. Sort or filter to identify the different patterns, make separate copies of the groups, and convert each group using its actual source order. For entries like 03/04/2026, Excel cannot infer your intended meaning from the characters alone. Check the original file, source system, or a known unambiguous date before converting.
Check the result and save. Verify several entries near the top, middle, and bottom of the selection. Compare a date with its source if possible, and make sure values have not shifted into another month or year. If the dates are correct but the column shows hashes, widen the column. Save the workbook after checking the converted values and formatting.

Common Mistakes

Assuming formatting converts text. Choosing a date format only changes how recognized date values appear. It will not reliably convert a text string such as 31/12/2026 into a date. Use Text to Columns with the correct source order, or correct the imported data first.
Picking the wrong regional order. A value such as 07/08/2026 can mean July 8 or August 7. Applying a different display format cannot resolve that ambiguity; conversion with the wrong MDY or DMY setting can create an incorrect date. Confirm the source convention before converting.
Confusing a display format with a date value. Showing a date as yyyy-mm-dd does not change the underlying date. Conversely, a number such as 46205 may be a valid date serial displayed with the General format. Switch it to a date format to check.
Changing the format of an entire column unnecessarily. This can affect numbers or text that are not dates, and may make unrelated values harder to read. Select only the cells that need the change, or use a dedicated date column.
Ignoring formulas and source data. A cell that contains a formula may display a number because of its number format; formatting the result is usually enough. If the formula returns text, a date format will not convert that text automatically. Check the formula bar and preserve the original data before editing imported values.

Tips

Choose an unambiguous format for shared files. A format such as yyyy-mm-dd puts the year first and avoids the common day/month versus month/day confusion. Written month names, such as 14 Mar 2026, are also easy for many readers to interpret.
Use the same display style throughout a column. Consistency makes sorting, reviewing, and printing easier. If the dates come from different sources, convert them to real dates first, then apply one format to the complete date range.
Remember that Excel dates are serial values. Excel stores recognized dates as numbers and displays them according to the chosen format. That is why the same value can appear as a short date, a long date, or a number without changing the date itself.
Keep a source copy when importing. A neighboring column with the untouched original text makes it easier to check how a conversion interpreted ambiguous entries. Remove the source copy only after you have verified the results and no longer need it.
Use the calendar picker only when appropriate. For a small number of entries, replacing a bad value by typing a clear date can be practical. For larger imported lists, use a controlled conversion method and verify the order rather than editing each cell by hand.

Troubleshooting

The date still looks the same after choosing Date. It may be text. Try the General-format check, then convert it using Text to Columns with the source’s date order. If conversion does not work, check for leading apostrophes, extra spaces, or inconsistent separators. Imported data can contain invisible spaces or a mixture of formats that needs cleaning before conversion.

The cell shows a number instead of a date. The value may be a real date displayed as General or Number. Select it and apply a date format. If the resulting date is far from the expected year, do not assume the display setting is the only issue; check whether the original value was interpreted incorrectly during import.

The cell shows ######. Excel commonly displays hashes when a column is too narrow to show the formatted date. Widen the column by dragging the boundary between its letter headings, or double-click that boundary to AutoFit. If the column is already wide enough, check whether the cell contains a negative date or time calculation, which standard date formats may not display as expected.

Dates switch between day and month or appear wrong on another computer. The workbook may be using a different regional setting, or the dates may still be text interpreted differently by the other system. Prefer a clearly specified format and convert text using the known source order. For an uncertain value, verify it against the original data rather than relying on how it looks after changing the format.

Text to Columns produces unexpected results. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac) if the conversion is wrong and has not been followed by other edits. Restore the source copy if needed, then repeat the conversion with the correct date order. If the entries have mixed patterns, separate them before converting. In Excel for the web, if the wizard or required options are unavailable, open the workbook in the desktop app and use Data > Text to Columns.

Frequently Asked Questions

How do I change the date format for an entire Excel column?
Select the column letter, press Ctrl+1 on Windows, choose Date or Custom on the Number tab, and click OK. Check that the column contains dates only, because the format can also affect other cell values.

Why does Excel show a five-digit number instead of a date?
Excel stores a recognized date as a serial number. The cell is likely using General or Number formatting. Apply a date format to display it as a calendar date, then check that the resulting day, month, and year are correct.

Can I change DD/MM/YYYY to MM/DD/YYYY without changing the date?
Yes, if the cell already contains a real date: select the format that uses the desired order. If the entry is text, convert it using the order in which the source was written. Formatting text alone does not safely reinterpret it.

What is the safest date format to share internationally?
A year-first format such as yyyy-mm-dd is commonly understood and avoids many day/month ambiguities. For reports read by a broad audience, a written month such as 14 Mar 2026 can also make the intended date clear.

Will changing the date format affect formulas or sorting?
Changing the display format alone does not change the stored date value, so formulas using a real date generally continue to work and dates sort chronologically. Text dates may sort alphabetically or behave differently in calculations until they are converted to actual Excel dates.

Can I fix dates in Excel for the web or on a Mac?
Yes. Excel for Mac has a Format Cells dialog, commonly opened with Command+1, while Excel for the web offers date choices on the Home tab. Some conversion controls differ by platform; use desktop Excel’s Text to Columns wizard if the web version does not provide the needed date-order option.

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.