The quickest way to change a date format

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 applies it when ready to your selected cells.

The format you choose changes only how the date appears on screen, not the actual date stored in the cell. If you change your mind, you can always go back and pick a different format using the same steps.

Key Takeaways

  • Select your date cells, right-click, and choose Format Cells to open the formatting dialog in one step.
  • The Date category in the Number tab shows you all available formats — pick the one that matches your needs.
  • 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 actual date value, only how it displays in the cell.
  • The same steps work whether your dates are in a single column, scattered across a sheet, or part of a larger data range.

Using the Format Cells dialog for common date formats

When you open Format Cells and select Date, Excel shows you a preview of how each format will look. Common formats include M/D/YYYY (3/15/2024), D-MMM-YY (15-Mar-24), and YYYY-MM-DD (2024-03-15). Scroll through the list to find the format your workplace, school, or country uses.

The locale setting at the bottom of the dialog affects which formats appear. If you're in the United States, you'll see US date formats by default. If you need a format from another country, you can change the locale dropdown to see formats used in that region.

Creating a custom date format when built-in options don't work

If none of the preset 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 field labeled Type or Format Code at the bottom. This field shows the code for the currently selected format — for example, M/D/YYYY or DDDD, MMMM D, YYYY.

You can edit this code directly. The letters represent different parts of the date: M is month, D is day, and Y is year. DDDD gives you the full day name (Monday), while DDD gives you the three-letter abbreviation (Mon). MMMM gives you the full month name (March), and MMM gives you the abbreviation (Mar). For example, to display dates as "Monday, March 15, 2024", use DDDD, MMMM D, YYYY. Click OK when you're done, and Excel saves your custom format.

Changing date format for an entire column at once

Click the column header letter to select the entire column. Then right-click and choose Format Cells. Select Date from the Category list and pick your format. This applies the format to every cell in that column, whether it contains a date now or you add dates later.

If you want to format multiple non-adjacent columns, hold Ctrl (or Command on Mac) and click each column header. Then right-click and format them all together using the same steps.

What to do if dates appear as numbers or error codes

Sometimes a date shows up as a long number like 45375 or as ##### symbols filling the cell. The number means Excel recognizes the value as a date but the column is too narrow to display it, or the format is set to General instead of Date. The ##### symbols mean the column is too narrow.

For the number problem, select the cells, open Format Cells, and change the Category from General to Date. For the ##### problem, double-click the border between two column headers to auto-fit the column width, or drag the border to make it wider manually. Once the column is wide enough, your date format will display correctly.

Formatting dates that are stored as text

If you copy dates from another program or source, Excel sometimes stores them as text instead of as actual date values. When this happens, the Format Cells dialog won't let you change them to a date format — the Date category will be grayed out or won't explore.

To fix this, select the cells and use the Text to Columns feature. Go to the Data tab and click Text to Columns. Click Next twice to skip the first two steps, then on the third screen make sure the column is set to Date format. Click Finish. Excel converts the text to actual dates, and you can then format them normally using Format Cells.

Using the Format menu as an alternative to right-clicking

If right-clicking doesn't work for you, you can reach the same Format Cells dialog through the menu. Select your date cells, then click the Home tab on the ribbon. In the far right of the toolbar, you'll see a small arrow next to the word Format. Click it and choose Format Cells. This opens the same dialog you'd get from right-clicking.

On older versions of Excel, the Format menu is in the menu bar at the top. Select your cells, click Format, then Cells. The dialog that opens works the same way.

Frequently Asked Questions

Can I change the date format for just one cell instead of a whole column?

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

Will changing the date format affect formulas that use these dates?

No. Formulas that reference dates will continue to work correctly because the actual date value in the cell doesn't change — only how it appears on screen. If a formula calculates the number of days between two dates, it will give the same result whether those dates are displayed as 3/15/2024 or 15-Mar-24.

What if I want to show the time along with the date?

In the Format Cells dialog, look for a format that includes both date and time, such as "3/15/2024 2:30 PM". If your cells contain both date and time information, these formats will display both. If they contain only dates, the time portion will show as 12:00 AM or 00:00.

How do I go back to the original date format if I don't like the new one?

Select the cells again, open Format Cells, and choose a different format from the Date category. You can switch between formats as many times as you want. If you want to return to how Excel displayed the dates when you first opened the file, select the cells and set the Category to General.

Can I save a custom date format so I can use it in other spreadsheets?

Custom formats are saved in the file where you create them, but not automatically in other files. If you create a custom format you use often, you can recreate it in any new spreadsheet by following the same steps: Format Cells, Date category, and edit the format code. Excel remembers custom formats you've used recently and shows them at the top of the format list.