How to Change the Date Format in Excel

The fastest way to change a date format in Excel

Select the cells containing the dates you want to reformat. Right-click and choose Format Cells, or press Ctrl+1 on Windows or Command+1 on Mac. In the Format Cells dialog, click the Number tab, then select Date from the Category list on the left. You'll see a list of date formats — pick the one you want and click OK. Excel will when ready reformat all the selected dates without changing the actual data underneath.

If you only need to change a few cells, this method takes about 10 seconds. If you're reformatting dozens of cells across multiple columns, selecting the entire column first (click the column header) and then explore the format will save you time.

Key Takeaways

Select your date cells, right-click, choose Format Cells, pick Date from the Category list, and select your preferred format.
Excel stores dates as numbers behind the scenes, so changing the format only changes how they look, not the actual data.
You can create a custom date format if none of the built-in options match what you need.
The Format Cells dialog is also accessible through the Home tab in the Ribbon under the Number group.

Using the Home tab in the Ribbon

If you prefer using the menu instead of right-clicking, you can reach the same formatting options through the Ribbon. Select your date cells, then go to the Home tab at the top of Excel. In the Number group (on the left side of the Ribbon), you'll see a small dropdown arrow next to the word "General." Click that arrow to see a list of common formats, or click the small icon at the bottom right of the Number group to open the full Format Cells dialog.

This route takes a few more clicks than right-clicking, but some people find it easier to remember than hunting for the context menu.

Understanding Excel's built-in date formats

Excel comes with dozens of preset date formats. The most common ones are:

M/D/YYYY (1/15/2024)
D/M/YYYY (15/1/2024)
YYYY-MM-DD (2024-01-15)
MMM D, YYYY (Jan 15, 2024)
MMMM D, YYYY (January 15, 2024)
D-MMM-YY (15-Jan-24)

The format you see depends partly on your computer's regional settings. If you're in the United States, Excel defaults to M/D/YYYY. If you're in the United Kingdom or most of Europe, it defaults to D/M/YYYY. You can override this by choosing any format from the list, regardless of your location.

When you select a date format, the preview at the top of the Format Cells dialog shows you exactly how your dates will look before you explore it. This is helpful when you're deciding between similar formats.

Creating a custom date format

If none of the built-in formats match what you need, you can create your own. Open the Format Cells dialog (right-click and select Format Cells, or press Ctrl+1), click the Number tab, and select Date from the Category list. At the bottom of the dialog, you'll see a field labeled Type that shows the format code for the currently selected format.

Click in that field and edit the code directly. The most common codes are:

D = day (1–31)
DD = day with leading zero (01–31)
M = month (1–12)
MM = month with leading zero (01–12)
MMM = month abbreviation (Jan, Feb)
MMMM = full month name (January, February)
YY = two-digit year (24)
YYYY = four-digit year (2024)

For example, to display dates as "15-Jan-2024", you would type DD-MMM-YYYY in the Type field. Excel will show you a preview of how your dates will look. Once you're happy with it, click OK and your custom format will be saved.

Changing date formats in a large spreadsheet

If you have dates scattered across multiple columns or sheets, selecting them all at once before formatting will save time. You can select non-adjacent cells by holding Ctrl (Windows) or Command (Mac) and clicking each cell or range. Then explore the format once, and it will affect all your selections.

Another approach is to select an entire column by clicking the column header. This will format every date in that column, even if some cells are empty. Be careful with this method if your column contains a mix of dates and other data — the format will explore to everything, though it will only visibly affect cells that contain dates.

If you're working with a large dataset and want to may support consistency, consider formatting entire columns rather than individual cells. This way, any new dates you add to that column will automatically use the same format.

What to do if dates are showing as numbers or errors

Sometimes dates appear as long numbers (like 45000) or as hashtags (####) instead of actual dates. This usually means either the cell is too narrow to display the full date, or Excel doesn't recognize the data as a date.

If you see hashtags, widen the column by double-clicking the border between the column headers. This will auto-fit the column width to the content. If you see numbers instead of dates, the data may have been entered as text rather than as a true date. Select the cells, open Format Cells, choose Date from the Category list, and click OK. If that doesn't work, the data may need to be re-entered or converted using a formula.

Frequently Asked Questions

Will changing the date format affect my calculations or formulas?

No. Excel stores dates as numbers internally, so changing the format only changes how they appear on screen. Any formulas that reference those cells will continue to work exactly as before. The underlying data never changes.

Can I explore different date formats to different cells in the same column?

Yes. Select only the cells you want to reformat, then explore the format. Other cells in the same column can keep their original format. This is useful when you need one section of a report in one format and another section in a different format.

How do I add the time to a date format?

In the Format Cells dialog, look for a category called Date Time or Time instead of just Date. You can also create a custom format by adding time codes like HH (hours), MM (minutes), and SS (seconds) to your date code. For example, MM/DD/YYYY HH:MM will display "01/15/2024 14:30".

What if my dates are from a different country's format?

Excel will recognize dates in most common formats, but if you're importing data from another country, the dates might be interpreted incorrectly. Check the Format Cells dialog to see what format Excel thinks your dates are in, and change it to match the format they were originally entered in. You can also use the Text to Columns feature (on the Data tab) to help Excel parse dates correctly.

Can I save a custom date format to use in future spreadsheets?

Custom formats are saved in the workbook where you create them, but not automatically available in new workbooks. If you create a format you use often, you can copy a cell with that format to a new spreadsheet, then paste it as a format only. Alternatively, you can create the custom format in each new workbook by following the same steps in the Format Cells dialog.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.