Excel Date Format: Change Defaults and Cell Settings

To change how dates appear in Excel, select the date cells, press Ctrl+1, choose Date or Custom on the Number tab, and select or enter a format. To change the default date style Excel picks up from your computer, update Windows regional date settings instead. These two changes do different things: cell formatting controls selected cells, while regional settings influence defaults and how some incoming dates are interpreted.

Changing a date format normally changes only its appearance, not the date value stored in the cell. For example, one date can display as 11/02/2026, 2-Nov-2026, or November 2, 2026, depending on the format and regional conventions. Before changing formats, check whether Excel recognizes the entries as dates; text that merely looks like a date may need to be converted first.

What You’ll Need

A Windows computer running Excel for Microsoft 365, if you want to follow the main instructions.
A workbook with dates entered or imported. If your goal is to change the computer-wide default, you may need permission to adjust Windows regional settings.
The date style you want, such as day/month/year or month/day/year. Decide whether you want a four-digit year and whether to use slashes, hyphens, or month names.

Step-by-Step

Select the date cells. Open the workbook and select the cells, column, or range whose display you want to change. To format a whole column, click its letter at the top; if the column also contains a heading or other values, consider selecting only the date cells so you do not change the heading’s format. You can hold Ctrl while selecting separate ranges on Windows. Formatting a range does not alter the underlying values.
Open the Format Cells dialog. Press Ctrl+1 on Windows. Alternatively, go to Home > Number and open the Number Format dropdown, then choose More Number Formats. In Excel for Mac, Command+1 opens Format Cells; the Home tab’s number-format controls are another option. This dialog is more precise than the short date buttons because it lets you inspect or choose a particular pattern.
Choose a built-in date style. On the Number tab, select Date in the Category list. Review the examples under Type and the Sample preview. Choose a style that shows the date in the order you expect. If available in the dialog, use Locale (location) to select the regional convention for the built-in date list. Then select OK. For example, a style with a day first is useful for day/month/year conventions, while a month-first style is common in the United States.
Use a custom format if the list does not match. In Format Cells, choose Custom under Category. Select an existing pattern to edit it, or type a format in the Type box, then check the Sample preview and choose OK. Common examples include dd/mm/yyyy for 02/11/2026, mm/dd/yyyy for 11/02/2026, yyyy-mm-dd for 2026-11-02, and d-mmm-yyyy for 2-Nov-2026. Excel’s display depends on the date value and the format code; the same value can be presented several ways without changing the stored date.
Check the result in the worksheet. Confirm that the displayed order and year are right, especially if the workbook will be shared with people using another regional convention. If the cell shows a row of number signs, widen the column. If it still shows the original text exactly, the entry may be stored as text rather than as an Excel date; see Troubleshooting before trying other display formats.
Change the Windows regional default only if that is your goal. Close Excel, open Start, search for Control Panel, and open it. Go to Clock and Region > Region > Additional settings, then open the Date tab. Change Short date to the preferred pattern and apply the change. Windows versions can label or arrange these controls slightly differently. This is a system-level setting that may affect other applications, not just Excel. Reopen Excel and check how newly entered or General-formatted dates appear. Existing cells with explicit formats may keep their current appearance.
Save the workbook and verify on the intended device. Save your changes, then consider reopening the file or checking a copy on the computer where it will be used. A workbook can contain dates with explicit cell formats, while cells using General or formats tied to a locale may display differently under another system’s regional settings. If consistency matters, apply a clear date format to the relevant range rather than relying only on each user’s defaults.

For a specific cell range, the first five steps are usually all you need. Changing Windows settings is not a substitute for formatting an existing range: it changes a broader preference, and it can affect how dates are shown or interpreted in other software. Avoid changing the system region just to make one worksheet column look different.

Common Mistakes

Confusing the display with the stored date. Applying a new format changes the appearance of a real date value; it does not convert the date into a different day. But if a value was imported as text, changing its number format may have no visible effect. Check the formula bar and try a simple test in a blank cell before reformatting a large range.
Mixing up day and month order. A value such as 04/05/2026 can mean April 5 or May 4 depending on the convention used when it was entered or imported. A date format can make a recognized date display in a chosen order, but it cannot reliably determine which meaning the original author intended. Confirm ambiguous source data before converting or sharing it.
Using a format that hides useful information. A format such as dd/mm omits the year. That can be convenient for a calendar view, but it may make records from different years appear identical. Keep the full year visible in data tables where sorting, review, or auditability matters.
Typing a format into the cell instead of the Type box. Entering something like dd/mm/yyyy in a worksheet cell types those characters as content; it does not set a display format. Enter custom patterns in Format Cells > Number > Custom > Type.
Expecting a Windows change to rewrite the workbook. Regional settings do not necessarily replace formats already assigned to cells. They also cannot resolve a date that was imported with the wrong day/month interpretation. Set the cells’ format directly when the workbook needs a dependable appearance.

Tips

Use an unambiguous style for shared files. A format with a month name, such as 2-Nov-2026, is easier to read across regions than a numeric date whose first two parts could be reversed. The ISO-style yyyy-mm-dd is also useful for sorting and data exchange, provided the receiving system handles it as expected.
Keep the year in operational records. Formats that show only a month and day can be attractive in reports, but include a year in the source data and consider showing it in working sheets to prevent confusion.
Format the whole intended range consistently. If new rows will be added, format the table’s date column rather than isolated cells. Excel tables often carry formatting into new rows, but verify the result when adding data from a paste or import.
Use the Sample preview before accepting a custom code. Excel date-format codes use m for month in date contexts. In a time format, m can represent minutes when it appears beside hours or seconds. If a code combines dates and times, check the preview carefully rather than assuming each letter has one meaning everywhere.
Preserve the original data before correcting ambiguous imports. Copy the source column to a spare column or make a workbook copy before transforming text dates. That gives you a way to compare the original values if the import’s regional convention turns out to be different from what you expected.
Do not change the date system casually. Excel’s 1900 and 1904 date systems are workbook calculation settings, not date display formats. Changing that setting can shift date values in a workbook, so it is not the right fix for a display preference. Leave it alone unless you are handling a workbook compatibility issue and understand the consequences.

Troubleshooting

The format changes, but the cell still looks unchanged. The cell may contain text. Select it and look at the formula bar; text imported from another system may include a leading apostrophe or spaces. Try changing one test entry by retyping it in a date form Excel recognizes for your region. If it then responds to date formatting, the original range needs conversion rather than a different display code.
The date becomes a number such as 46328. Excel stores recognized dates as serial numbers. The number is the stored value shown without a date display format. Select the cells and apply a Date format or a custom pattern using Ctrl+1. If you expected a particular day, verify the value rather than assuming the number is an error.
You see ##### instead of a date. The column is usually too narrow to show the formatted value. Widen it by dragging the right edge of the column letter, or double-click that edge to AutoFit. If widening does not help, check for a negative date or time value, which may not display as expected under the selected date system.
The date is interpreted as the wrong day or month. This commonly happens when a file was created under a different regional convention. Do not simply apply a new format and assume the underlying date is now correct. Compare the original source, confirm the intended order with its owner, and convert the data using the correct interpretation. Keep an untouched copy until you have verified several examples.
New dates still use the old default after changing Windows. Confirm that the Regional settings change was applied, then close and reopen Excel. Check a new blank cell and make sure its number format is General before testing; an existing cell may retain its own format. On a managed work computer, organizational policy may control regional settings, so ask your administrator if the option is unavailable or reverts.
The same workbook looks different on another computer. Check whether the cells have explicit date formats or depend on General and local defaults. Apply an explicit, suitable format to the date range, save the workbook, and reopen it on the other computer. Also confirm the recipient’s Excel or spreadsheet software supports the workbook’s formatting.

In Excel for the web, select the cells and use the Home tab’s Number Format controls to apply a date style. The exact choices can differ from desktop Excel, and some detailed formatting controls may be more limited. For custom patterns or Windows-wide regional defaults, open the workbook in the desktop app or change the computer’s regional settings as appropriate. On Mac, use the Format Cells dialog for cell-level formatting; macOS regional preferences are managed separately through System Settings > General > Language & Region.

Frequently Asked Questions

Does changing the date format change the actual date?

No. For a date Excel recognizes, formatting changes how the date is displayed, not the stored date value. If the entry is text, however, it may not respond to date formatting and may need to be converted first.

How do I make Excel show day/month/year?

Select the date cells, press Ctrl+1, choose Custom, and enter dd/mm/yyyy in Type. Review the Sample preview and click OK. This controls the display for the selected cells; it does not change how other applications format dates.

Can I set one default date format for every Excel workbook?

Excel generally takes default date conventions from the computer’s regional settings, while cells and workbooks can have their own formats. There is not one simple Excel setting that changes every existing date cell in every workbook. For dependable results, format the relevant ranges in each workbook.

Why does Excel show a number instead of a date?

Excel stores recognized dates as serial numbers. If a cell is set to General or Number, you may see that underlying serial value. Apply a Date or Custom format. If the value does not behave like a date afterward, it may be text rather than a recognized date.

Will a custom date format work on Mac or Excel for the web?

Excel for Mac supports cell date formatting through Format Cells, commonly opened with Command+1. Excel for the web provides date formats in its Home tab, but its detailed options can differ from desktop Excel. For a custom format that is missing online, open the workbook in desktop Excel.

Should I change Excel’s 1900 or 1904 date system to change the date display?

No. Those settings determine how date serial values are based and can affect workbook values; they are not display-format choices. Use the Number tab in Format Cells to change how a date appears.

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.