Changing Date Format in Excel: Steps, Options, and Fixes

How-to basics · Updated November 18, 2026

To change a date’s appearance in Excel for Microsoft 365 on Windows, select the date cells, go to Home > Number Format, and choose a date style. For more control, press Ctrl+1, open the Number tab, and choose Date or Custom; this changes how a valid date is displayed, not the date value itself.

Change a date format with the ribbon

The ribbon is the quickest way to switch among common formats such as a short date or a longer date that spells out the month. Select one cell or a range, then use the Number Format dropdown in the Number group on the Home tab. Choose Short Date or Long Date. The exact appearance can depend on your Windows regional settings: a short date might show as 3/7/2026 in one region and 07/03/2026 in another.

If the dropdown does not show the style you want, open the Format Cells dialog. On Windows, Ctrl+1 opens it; choose the Number tab and then Date to see date formats, or Custom to enter a format code. On a Mac, Command+1 opens Format Cells. You can also reach the dialog from Home > Number group using the small dialog box launcher. In Excel for the web, select the cells and use Home > Number Format; the desktop app offers the fuller set of custom-format controls.

To apply a format to an entire column, click its column letter before choosing the format. You can also select just the rows containing dates. Formatting a whole column is convenient when new dates will be added later, though it does not turn future text entries into actual dates.

Choose a format that suits the task

A useful date format makes the value easy to read while avoiding uncertainty. For example, 04/05/2026 could mean April 5 or May 4, depending on regional convention. If a file will be shared across countries, a format such as 2026-04-05 is less ambiguous. For a report aimed at people, “April 5, 2026” may be easier to scan.

  • Short dates: compact and useful in tables, but often dependent on regional settings.
  • Long dates: include the month name and are easier to interpret, but take up more space.
  • Year-month-day: a clear choice for cross-region files and date lists that need to sort chronologically as text.
  • Month and year: suitable for summaries where the day is not important, such as monthly reporting.
  • Day of the week: useful for schedules; a custom display can show the weekday alongside the date.

In a custom format, d displays the day as a number and dd adds a leading zero. The codes ddd and dddd display an abbreviated or full weekday name. For months, m or mm displays a number, while mmm or mmmm displays an abbreviated or full month name. The codes yy and yyyy display a two-digit or four-digit year. For instance, the custom code dddd, mmmm d, yyyy can display a date as Saturday, April 5, 2026, if that date falls on a Saturday. The format changes the display; it does not add weekday text to the stored value.

How Excel stores dates

Excel treats a genuine date as a number that can be formatted for display. In the standard 1900 date system, dates are represented by serial numbers, with later dates having larger serial values; times can be represented as fractional parts of a day. That underlying numeric value lets Excel sort dates and use them in calculations. A cell may therefore show a date while still containing a number that Excel can compare or calculate with.

This distinction explains why changing a format usually does not alter the date. If a cell contains a valid date and you switch from a short date to a long date, Excel displays the same underlying value in a different way. If you change the format to General, you may see a serial number instead. That is not necessarily a conversion error: it can reveal the number stored underneath the date appearance.

Excel workbooks can use either the 1900 or 1904 date system. The systems assign different serial numbers to the same calendar date, so changing a workbook’s date-system setting can shift displayed dates by 1,462 days. Avoid changing that setting as a routine fix. It can affect date values throughout the workbook, and is most relevant when moving data between older or specially configured workbooks.

Use custom formats for precise displays

Choose Home > Number Format > More Number Formats, or press Ctrl+1 and select Number > Custom. In the Type box, enter a format code and check the sample shown in the dialog before applying it. A code such as yyyy-mm-dd presents a four-digit year, two-digit month, and two-digit day. A code such as mmm d, yyyy displays an abbreviated month, day, and four-digit year.

Custom formats are useful when a workbook needs a consistent display without changing its values. They can show a month name, add leading zeroes, or include a weekday. If a format code does not appear as expected, verify that the cell contains an actual date and that the code is appropriate for your Excel language and regional settings. Format-code conventions can vary by locale, so preview the result in the dialog rather than assuming a code will look identical on every computer.

A custom number format is not the same as typing extra characters into the cell. Formatting a date to show “Apr 2026,” for example, can leave the underlying day intact even though the day is hidden. That matters if the cell is later used in calculations or grouped by month. If you need the displayed text as a separate text value for a report or export, that is a different task from changing the cell’s number format.

When a date is stored as text

Changing the number format will not fix a value Excel sees as text. A pasted entry such as 31/12/2026 may remain unchanged when you select Date, especially if the computer expects month/day/year and cannot interpret the text as a valid date. A common clue is that the value does not respond to date formatting or behaves differently from nearby dates when sorted.

First check how the value was entered or imported. If a cell has a warning indicator, select it and inspect Excel’s suggested conversion options. For a structured text import, use Data > Text to Columns. Choose Delimited, advance through the wizard, select the date column, and set Column data format to Date with the order used by the source data, such as DMY or MDY, before finishing. Make a copy or test on a few rows first: selecting the wrong order can turn an ambiguous date into a different calendar day.

Do not assume every slash-separated value has the same interpretation. In a U.S.-style MDY file, 03/04/2026 commonly means March 4; in a DMY file, it commonly means 3 April. If the day is greater than 12, it may help identify a DMY value, but dates with both first numbers at or below 12 remain ambiguous. Confirm the source’s convention rather than guessing. For CSV files, check the import settings and regional assumptions before saving; a displayed format alone cannot repair an incorrectly interpreted date.

Common display problems and their fixes

  • The cell shows a number: the value may be a valid date displayed in General or another numeric format. Apply a date format. If the number is unexpected, check whether the cell contains the intended date and whether the workbook’s date system matters.
  • The date does not change: Excel may be treating the entry as text. Confirm the value is a real date and convert or re-import it using the source’s date order.
  • The cell shows ####: the column may be too narrow for the chosen display. Widen it by dragging the boundary between column letters or double-clicking that boundary to AutoFit. A negative date or time calculation can also produce hash marks in some circumstances, so inspect the underlying value if widening does not help.
  • The day and month appear reversed: the date may have been interpreted using a different regional order. Changing the display format cannot reliably correct a value that was parsed as the wrong day.
  • Only some cells change: the range may contain a mixture of real dates, text, and blanks, or cells may have different formats. Check representative cells individually before applying a bulk conversion.

When dates seem to change after opening a file on another computer, distinguish between a display difference and a changed value. Regional settings can make a short date look different while preserving its underlying date. But if an ambiguous text value was interpreted differently during import, the underlying calendar date may actually be wrong. A clear format such as yyyy-mm-dd helps communicate a value; it does not reverse a mistaken import.

Apply formats consistently and safely

To copy only the appearance from one correctly formatted date cell, copy it, select the destination cells, and use Home > Paste > Paste Special > Formats. This leaves destination values in place while applying the source’s formatting. The Format Painter on the Home tab is another option for copying appearance. Check the destination afterward, especially if it contains text or values that are not dates.

For a dependable shared workbook, decide on one display convention and apply it across relevant columns. Include a column heading that describes the data, and avoid mixing ambiguous forms such as 3/4/26 and 04/03/2026 in the same table. When dates are important for sorting, filtering, or calculations, keep them as actual Excel dates rather than converting them to text just to obtain a particular look. If exporting data to another system, follow that system’s required format and verify a sample after export.

Before making a large conversion to imported data, preserve the original column or save a copy of the workbook. A display-format change is normally reversible, but a conversion based on the wrong date order can change the interpreted value. Keeping the source makes it easier to compare and correct the result without reconstructing the original entries.

Frequently Asked Questions

How do I change the date format in Excel without changing the date?
Select the cells and choose a different style under Home > Number Format, or use Ctrl+1 and select Date or Custom. This changes the display for genuine date values, not the stored date.

How do I show the month name instead of a number?
Open Format Cells with Ctrl+1, choose Number > Custom, and use a code such as mmmm d, yyyy for a full month name or mmm d, yyyy for an abbreviation. Preview the result before applying it.

Why does Excel show a number instead of a date?
Excel may be displaying the date’s serial value because the cell is formatted as General or Number. Apply a date format. If that does not produce a sensible date, the value may not be a valid date or the workbook may use a different date system.

Why won’t Excel change the format of my date?
The entry may be text rather than an Excel date, often because it was pasted or imported using a different day-month order. Convert it using the correct source convention, or import it with Data > Text to Columns and select the appropriate date order.

How can I make dates display as yyyy-mm-dd?
Select the cells, open Format Cells with Ctrl+1, choose Number > Custom, enter yyyy-mm-dd in Type, and confirm the preview. The displayed style is clear across regions, but the cells still need to contain valid Excel dates.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.