The fastest way to change how a date displays

Excel stores dates as numbers behind the scenes, so changing how they look on screen takes seconds. Select the cells with the dates you want to reformat, 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. Click OK. The dates themselves don't change — only how they appear.

If you're working with a small number of cells, you can also use the Format menu at the top instead of right-clicking. The result is the same either way.

Key Takeaways

  • Right-click the cells with dates, choose Format Cells, pick Date from the Category list, and select your preferred format.
  • Excel's built-in date formats include options like MM/DD/YYYY, DD/MM/YYYY, and text versions like "January 15, 2024".
  • If a date shows as a number like 45000, it's formatted as a number instead of a date — use the same Format Cells method to fix it.
  • Custom formats let you create date displays that don't match Excel's standard options, using codes like YYYY for year and MMM for abbreviated month.

Using the Format Cells dialog to pick a standard date format

The Format Cells dialog is where Excel keeps all its preset date formats. To open it, select your date cells, right-click, and choose Format Cells. On the Number tab, click Date in the Category list. You'll see a list of formats on the right side of the dialog.

Common formats include 3/15/24 (short date with two-digit year), 3/15/2024 (short date with four-digit year), Tuesday, March 15, 2024 (long date with day name), and 15-Mar-24 (abbreviated month). The preview at the top of the dialog shows how your selected cells will look with each format you highlight.

If you work with international teams or need dates in a specific regional format, this dialog also shows locale-specific options. For example, some regions default to DD/MM/YYYY while others use MM/DD/YYYY. The dialog respects your computer's regional settings but lets you override them for this spreadsheet.

What to do if dates are showing as numbers

Sometimes a date appears as a long number like 45000 instead of a readable date. This happens when Excel is treating the cell as a number rather than a date. The fix is the same: select the cells, right-click, choose Format Cells, click Date in the Category list, pick a format, and click OK.

If the number still shows after you explore a date format, the original data may not have been entered as a date. This can happen when dates are pasted from another program or typed in a way Excel doesn't recognize. In that case, you may need to re-enter the dates or use a formula to convert them — but try the Format Cells method first, because it works most of the time.

Creating a custom date format for non-standard displays

If none of Excel's built-in formats match what you need, you can build your own. In the Format Cells dialog, select Date from the Category list, then scroll to the bottom and click User-Defined. In the Format Code box at the top, you'll see the code for the currently selected format.

Common codes are YYYY for a four-digit year, YY for two digits, MM for month as a number, MMM for abbreviated month name (Jan, Feb), MMMM for full month name, DD for day, and DDDD for full day name. You can combine them with slashes, hyphens, or spaces. For example, MMMM DD, YYYY produces "March 15, 2024" and DD-MMM-YY produces "15-Mar-24".

Type your custom code into the Format Code box and click OK. Your custom format will appear in the User-Defined category the next time you open Format Cells, so you can reuse it on other cells without retyping it.

Using keyboard shortcuts to speed up formatting

If you format dates often, keyboard shortcuts save time. On Windows, select your date cells and press Ctrl+1 to open the Format Cells dialog directly — no right-click needed. On Mac, press Cmd+1. From there, follow the same steps: click Date, pick your format, click OK.

You can also use the Format menu at the top of the screen. Click Format, then Cells, then proceed as usual. This route works the same way but takes a few more clicks than the keyboard shortcut.

Formatting dates in a whole column at once

To format every date in a column, click the column header (the letter at the very top) to select the entire column. Then right-click, choose Format Cells, select Date, pick your format, and click OK. Every cell in that column will now display dates in the format you chose.

Be careful when formatting an entire column — if the column contains text or numbers that aren't dates, they may display strangely or show as errors. If that happens, undo the change by pressing Ctrl+Z (or Cmd+Z on Mac), then select only the cells that actually contain dates before formatting again.

Frequently Asked Questions

Why does my date still look wrong after I change the format?

The column might be too narrow to display the full date. Widen the column by double-clicking the border between column headers — Excel will auto-fit the width. If the date still looks wrong, check that you selected the right cells before formatting. You may also need to scroll left or right to see the column you formatted.

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 non-adjacent cells by holding Ctrl (or Cmd on Mac) and clicking each one, then format them all at once.

What if I want dates to show with a time, like "3/15/2024 2:30 PM"?

In the Format Cells dialog, select Date from the Category list and look for a format that includes time. If you don't see one, click User-Defined and type a custom code like MM/DD/YYYY HH:MM AM/PM. The HH:MM part controls the time display.

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

No. Formatting only changes how the date looks on screen. The underlying date value stays the same, so formulas that use those cells will still work correctly. If you copy the cell and paste it elsewhere, the date value comes with it.

How do I change the date format back to the default?

Select the cells, right-click, choose Format Cells, select Date, and pick the standard format you want (usually MM/DD/YYYY or DD/MM/YYYY depending on your region). Click OK. If you want to undo all formatting changes to a cell, select it and press Ctrl+M (Windows) or Cmd+M (Mac).