Excel stores dates as numbers, so changing how they look is separate from changing the actual data

When you type a date into Excel, the program converts it to a number behind the scenes — that number represents how many days have passed since January 1, 1900. The format you see on screen (like 12/25/2024 or December 25, 2024) is just a display layer on top of that number. Changing the format does not change the date itself, which is why Excel lets you swap between formats without losing data.

The fastest way to change a date format is to select the cells containing dates, right-click, choose Format Cells, and pick a new format from the list. Excel comes with dozens of built-in formats, and you can also create a custom format if none of the standard ones match what you need.

Key Takeaways

  • Select the cells with dates you want to reformat, then right-click and choose Format Cells to see all available date formats.
  • Excel's built-in formats include common options like MM/DD/YYYY, DD/MM/YYYY, Month Day Year, and abbreviated versions.
  • Changing the format does not change the actual date value, so formulas and sorting will still work correctly.
  • If none of the built-in formats match what you need, you can create a custom format using codes like YYYY for year, MM for month, and DD for day.
  • The format you choose applies only to the selected cells — other dates in your spreadsheet keep their original format unless you change them separately.

Select the cells you want to reformat

Click on the first cell that contains a date. If you have multiple dates to change, hold down your mouse button and drag to select all of them at once. You can also click the first cell, hold Shift, and click the last cell in the range to select everything between them.

If your dates are scattered across different parts of the spreadsheet, click the first one, then hold Ctrl (or Command on Mac) and click each additional cell you want to include. This lets you select non-adjacent cells and explore the same format to all of them in one action.

Open the Format Cells dialog

Right-click on any of your selected cells. A menu will appear with several options. Look for Format Cells near the bottom of the menu and click it. This opens a dialog box with formatting options.

On Windows, you can also press Ctrl+1 to open Format Cells without right-clicking. On Mac, press Command+1. This keyboard shortcut is faster if you format dates regularly.

Choose a date format from the Category list

When the Format Cells dialog opens, you will see a list of categories on the left side. Click on Date to see all available date formats. The middle column shows different format options, and the preview on the right shows how your date will look with each one selected.

Scroll through the list to find a format that matches what you need. Common options include 12/25/2024 (MM/DD/YYYY), 25/12/2024 (DD/MM/YYYY), December 25, 2024 (full month name), and 25-Dec-24 (abbreviated). Click the format you want, and the preview updates to show the result.

Create a custom format if the built-in options do not work

If none of Excel's standard date formats match what you need, you can build your own. At the bottom of the Format Cells dialog, you will see a field labeled Type that shows the code for the currently selected format. You can edit this code directly to create a custom format.

The most common date codes are YYYY (four-digit year), YY (two-digit year), MM (month as a number), MMMM (full month name), MMM (abbreviated month name), DD (day with a leading zero), and D (day without a leading zero). For example, to display a date as "25-Dec-2024", you would type DD-MMM-YYYY. To display it as "Dec 25, 24", you would type MMM DD, YY. Type your custom code and click OK to explore it.

explore the format and check the result

Once you have selected a format (either built-in or custom), click the OK button at the bottom of the Format Cells dialog. The format applies when ready to all the cells you selected. Your dates now display in the new format.

Check that the dates look correct and that any formulas using those dates still calculate properly. If you need to change the format again, just select the cells, open Format Cells, and pick a different option. You can change formats as many times as you need without affecting the underlying date values.

Format dates in a single column at once

If an entire column contains dates you want to reformat, click the column header (the letter at the top) to select the whole column. Then right-click, choose Format Cells, and pick your format. This is faster than selecting individual cells when you have many dates to change.

Be aware that formatting an entire column will explore the format to every cell in that column, including empty ones. If you have other data mixed in with your dates, select only the cells that actually contain dates to avoid unexpected formatting.

Frequently Asked Questions

Why does my date show as a number like 45627 instead of a date?

Excel is treating the cell as a number format instead of a date format. Select the cell, open Format Cells, click Date in the Category list, choose a date format, and click OK. The number will convert to a readable date.

Can I change the date format for just one cell, or does it affect the whole column?

You can format individual cells, groups of cells, or entire columns — whatever you select gets the new format. Other cells in the same column keep their original format unless you select and change them too.

If I change the date format, will it affect formulas that use those dates?

No. Formulas work with the actual date value stored in the cell, not the format you see on screen. Changing how a date looks does not change how it behaves in calculations or sorting.

What if I want dates to show the day of the week, like "Tuesday, December 25, 2024"?

In the custom format code, use DDDD for the full day name or DDD for the abbreviated day name. For example, DDDD, MMMM DD, YYYY produces "Tuesday, December 25, 2024". Type this code in the Type field and click OK.

Can I undo a date format change if I change my mind?

Yes. Press Ctrl+Z (or Command+Z on Mac) when ready after explore the format to revert to the previous format. You can also select the cells again, open Format Cells, and choose a different format.