The fastest way to change a date format in Excel

Right-click the cell or column containing the date, select Format Cells, click the Number tab, choose Date from the Category list on the left, and pick the format you want from the list. Click OK. The dates will display in the new format immediately, though the underlying data does not change.

If you need to change many cells at once, select the entire range first — click the first cell, hold Shift, and click the last cell in the range you want to format. Then right-click and follow the same steps. Excel will apply the format to all selected cells.

You can also use the Format menu instead of right-clicking. Click Format in the menu bar, select Cells, and proceed the same way. The result is identical.

Key Takeaways

  • Right-click a date cell, choose Format Cells, select Date from the Category list, and pick your format from the options shown.
  • Changing the format changes only how the date appears on screen, not the actual data stored in the cell.
  • You can format a single cell, a range of cells, or an entire column all at once by selecting them before you open Format Cells.
  • Excel includes dozens of built-in date formats, and you can create a custom format if none of the standard ones match what you need.

Understanding what the Format Cells dialog shows you

When you open Format Cells and select Date from the Category list, the middle column shows you a preview of how your dates will look. The right side shows the format code — a string of letters and symbols like mm/dd/yyyy or dddd, mmmm d, yyyy. These codes tell Excel exactly how to display the date.

The Locale dropdown at the bottom lets you choose a region, which changes which date formats are available. For example, selecting a European locale will show formats like dd/mm/yyyy instead of mm/dd/yyyy. This is useful if you work with international teams or need to match a specific country's date convention.

The preview at the top of the dialog shows you exactly what your dates will look like after you apply the format. If the preview does not match what you want, scroll through the list until you find one that does.

Creating a custom date format when built-in options do not work

If none of Excel's standard date formats match what you need, you can build your own. In the Format Cells dialog, select Date from the Category list, then look for a format code that is close to what you want. Click in the Type field at the top and edit the code directly.

Common format code symbols include: d for day (1–31), dd for day with a leading zero (01–31), m for month (1–12), mm for month with a leading zero (01–12), yyyy for four-digit year, and yy for two-digit year. You can also add text — for example, mmmm d, yyyy displays as "January 15, 2024".

As you edit the code, the preview updates to show you what the result will look like. Once you are satisfied, click OK. Your custom format is now saved in that workbook and you can reuse it on other cells.

Formatting dates in an entire column at once

Click the column header (the letter at the top) to select the entire column. Then right-click and choose Format Cells. Select Date from the Category list and pick your format. Click OK. Every date in that column will now display in the format you chose.

This approach is useful when you import data and all the dates come in with the wrong format. Instead of fixing each cell individually, you can fix the whole column in one step. Any new dates you enter in that column will also use the same format automatically.

If you want to format only some cells in a column, select just those cells instead of the entire column. Click the first cell, hold Shift, and click the last cell in the range you want to change.

Why dates sometimes display as numbers or errors after formatting

If you change a date format and the cell shows a number like 45000 or an error like #####, one of two things is happening. The ##### error usually means the column is too narrow to display the formatted date. Double-click the border between the column headers to auto-fit the column width, and the date will appear correctly.

If the cell shows a number instead of a date, the cell may not actually contain a date — it contains text that looks like a date. Excel stores real dates as numbers (the number of days since January 1, 1900), and it can only format actual dates. Text that resembles a date will not format as a date. You can check by clicking the cell and looking at the formula bar; if it shows an apostrophe at the start, it is text.

To convert text that looks like a date into a real date, you may need to use a function like DATEVALUE or reimport the data with the correct settings. This is a separate task from formatting, but it is a common reason dates do not format the way you expect.

Copying a date format to other cells without copying the data

Excel has a tool called Format Painter that copies formatting from one cell to others without changing the data. Click the cell with the date format you want to copy, then click the Format Painter button in the toolbar (it looks like a paintbrush). Your cursor will change. Click and drag across the cells you want to apply that format to, and release.

If you need to apply the format to multiple separate ranges, click the Format Painter button twice instead of once. The first click activates it, and the second click keeps it active so you can paint multiple areas. Press Escape when you are done.

This method is faster than opening Format Cells multiple times if you have already set up the exact format you want on one cell and just need to spread it around.

Frequently Asked Questions

Will changing the date format change the actual date stored in the cell?

No. Formatting only changes how the date appears on screen. The underlying data remains the same. If you change a date from mm/dd/yyyy to dd/mm/yyyy, the cell still contains the same date — it just looks different. Formulas that reference that cell will still work correctly.

Can I format dates in Excel on a Mac?

Yes. Right-click the cell, select Format Cells, click the Number tab, choose Date from the Category list, and pick your format. The steps are the same as on Windows. The Format Cells dialog looks slightly different on Mac, but the options and results are identical.

What if I want dates to show the day of the week along with the date?

In Format Cells, select Date from the Category list and look for a format that includes the day name, such as "dddd, mmmm d, yyyy" (which displays as "Monday, January 15, 2024"). If you do not see one you like, you can edit the format code directly to add dddd for the full day name or ddd for the three-letter abbreviation.

Why does my date format change back after I close and reopen the file?

This usually happens if you did not save the file after formatting. Make sure to save the workbook (Ctrl+S or Cmd+S) after you apply the format. The format is part of the file and will be preserved when you reopen it. If the format still reverts, check whether the file is saved as .xlsx (Excel format) rather than .csv or another format that does not preserve formatting.