The fastest way to change how dates look in Excel

To change a date format in Excel, select the cells containing the dates, right-click, choose Format Cells, click the Number tab, select Date from the Category list on the left, then pick the format you want from the list on the right. Click OK. The dates will display in your chosen format without changing the actual data underneath.

This method works the same way in Excel for Windows and Excel for Mac. The dates themselves stay the same—only how they appear on screen changes. You can undo the change at any time by pressing Ctrl+Z (or Cmd+Z on Mac).

Key Takeaways

  • Select the cells with dates, right-click, and choose Format Cells to open the formatting menu.
  • Excel offers dozens of built-in date formats, from "1/15/2024" to "January 15, 2024" to "15-Jan-24".
  • You can create a custom date format if none of the built-in options match what you need.
  • Changing the format does not change the date value itself, only how it displays in the cell.

Step-by-step: Using the Format Cells dialog

Start by clicking on any cell that contains a date you want to reformat. Then hold Shift and click on the last cell in the range you want to change. This selects all the cells between them. If the dates are not in a continuous block, hold Ctrl (or Cmd on Mac) and click each cell individually to select multiple separate cells.

Right-click anywhere in your selection and click Format Cells from the menu that appears. A dialog box will open. Make sure you are on the Number tab—it is usually the first tab. In the Category list on the left side, click Date. The middle column will now show all available date formats. Scroll through and click the one you want. The preview at the bottom shows how your dates will look. Once you find the format you want, click OK.

Your dates will now display in the new format. If you selected multiple cells with different dates, they will all change to the same format style.

Common date formats and when to use them

Excel includes formats for nearly every way people write dates. The M/D/YYYY format (like 1/15/2024) is standard in the United States. D/M/YYYY (like 15/1/2024) is common in Europe and many other countries. YYYY-MM-DD (like 2024-01-15) is used in technical work and databases because it sorts correctly alphabetically.

If you want the month spelled out, formats like January 15, 2024 or 15 January 2024 are available. Shorter versions like Jan 15, 2024 or 15-Jan-24 save space while staying readable. Some formats include the day of the week: Tuesday, January 15, 2024. Choose based on where the spreadsheet will be used and who will read it.

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 the same way—select cells, right-click, choose Format Cells. Click the Number tab and select Date from the Category list. At the bottom of the dialog, you will see a field labeled Type that shows a code like m/d/yyyy. You can edit this code directly.

Use d for day, m for month, and y for year. Add more letters to change how they display: dd shows the day with a leading zero (01 instead of 1), mmmm spells out the full month name, and yyyy shows the four-digit year. For example, typing mmmm d, yyyy creates the format "January 15, 2024". Type your custom code and click OK.

Why dates sometimes show as numbers or ####

If a cell shows a long number like 45,321 instead of a date, the cell is formatted as a number rather than a date. Excel stores dates as numbers internally, so this is the raw value showing through. Select the cell, open Format Cells, choose Date from the Category list, and click OK. The number will convert to a readable date.

If a cell shows #### instead of a date, the column is too narrow to display the full date. Double-click the border between the column headers to auto-fit the column width, or drag the border to the right to make it wider manually. The date will then appear normally.

Changing date format for an entire column

To format all dates in a column at once, click the column header letter (A, B, C, and so on) to select the entire column. Right-click and choose Format Cells. Select Date from the Category list, pick your format, and click OK. Every cell in that column will now use the new date format.

This is useful when you have a spreadsheet with many rows of data. You do not have to select each cell individually—one click on the column header selects everything. Be aware that this will change the format of any dates in that column, even ones you add later, until you change the format again.

Using the Format menu instead of right-click

If right-clicking does not work or you prefer using the menu bar, you can reach the same dialog through the ribbon. Select your cells, then click the Home tab at the top. In the toolbar, look for a small arrow next to the word Format (or a paintbrush icon in some versions). Click it and choose Format Cells. This opens the same dialog as right-clicking.

On Mac, select your cells and click Format in the menu bar at the top of the screen, then choose Cells. The Format Cells dialog will open the same way.

Frequently Asked Questions

Can I change the date format for just one cell?

Yes. Click the single cell, right-click, choose Format Cells, select Date, pick your format, and click OK. You can also select multiple individual cells by holding Ctrl (or Cmd on Mac) and clicking each one, then format them all at once.

Will changing the date format affect my calculations?

No. Changing how a date looks does not change the date value itself. Formulas that use those dates will work exactly the same way. Only the display changes.

What if I want dates to show the time as well?

In the Format Cells dialog, look for a category called Date Time or Time instead of just Date. This will show formats that include both the date and the time, like "1/15/2024 2:30 PM".

How do I undo a date format change?

Press Ctrl+Z (or Cmd+Z on Mac) when ready after making the change. This will revert the cells to their previous format. You can undo multiple steps by pressing the shortcut several times.

Can I save a custom date format to use again later?

Custom formats you create are saved in that workbook, but they do not transfer to other files automatically. If you create a format you use often, write down the code (like "mmmm d, yyyy") so you can type it again in other spreadsheets.