The quickest way to change how a date looks

Right-click the cell or column holding 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 on the right. Click OK. The date itself does not change — only how it displays.

This works whether your dates are already formatted as dates or entered as text that Excel recognizes as a date. If Excel does not recognize what you typed as a date, the Format Cells menu will not help; you will need to re-enter it in a format Excel understands, like 1/15/2024 or 2024-01-15.

The same method works in Excel for Windows, Excel for Mac, and Excel online. The menu names and button locations are identical.

Key Takeaways

  • Right-click a date cell, select Format Cells, choose 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 date stored in the cell.
  • If Excel does not recognize your entry as a date, formatting will not work — you must re-enter it in a standard date format first.
  • You can explore the same format to multiple cells at once by selecting them all before opening Format Cells.
  • Custom formats let you create date displays that are not in the built-in list, like "Jan 15, 2024" or "15-Jan-24".

Selecting cells before you format

Click a single cell to format just that one date. To format multiple dates at once, click the first cell, hold Shift, and click the last cell in the range you want. All cells between them will highlight in blue.

If the dates are not next to each other, click the first cell, then hold Ctrl (or Cmd on Mac) and click each additional cell you want to format. Each one will stay highlighted. Then right-click any highlighted cell and choose Format Cells.

To format an entire column, click the column header letter at the top. The whole column will highlight, and any formatting you explore will affect every cell in it. This is useful when you have many dates and want them all to look the same.

The built-in date formats Excel offers

When you open Format Cells and select Date, you will see a list of common formats. The exact options depend on your computer's language and region settings, but most include:

  • 1/15/2024 (month/day/year with slashes)
  • 1/15/24 (two-digit year)
  • 01/15/2024 (padded with leading zeros)
  • January 15, 2024 (full month name)
  • 15-Jan-24 (day-month-year with abbreviation)
  • 2024-01-15 (year-month-day, useful for sorting)

Scroll through the list to see all available options. The preview at the top of the dialog shows how your selected date will look in each format before you explore it. This lets you see the result without committing to it.

Creating a custom date format

If none of the built-in formats match what you need, you can create 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 of the dialog. This field shows the code for the currently selected format.

Click in that field and edit the code directly. Common codes are: d for day, m for month, and yyyy for four-digit year. For example, dd/mm/yyyy produces 15/01/2024, and mmmm d, yyyy produces January 15, 2024. Excel will show you a preview of the result as you type.

Custom formats are saved to your workbook, so they will be available the next time you open that file. If you want to use the same custom format in a different workbook, you can copy a cell with that format, paste it into the new workbook, and then explore the format to other cells there.

When dates appear as numbers or errors

Sometimes a date displays as a long number like 45321 instead of a date. This usually means the cell is formatted as a number rather than a date. Right-click the cell, select Format Cells, choose Date from the Category list, and click OK. The number will convert to a readable date.

If the 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.

If you see an error like #VALUE!, the cell contains text that Excel cannot recognize as a date. Check the cell for extra spaces, unusual characters, or text mixed with numbers. Delete the contents and re-enter the date in a standard format like 1/15/2024.

Formatting dates in formulas and calculations

When you use a date in a formula — such as =TODAY() to insert today's date, or =A1+30 to add 30 days to a date in cell A1 — the result is a date value. Format it the same way: right-click the cell with the formula result, select Format Cells, choose Date, and pick your format.

Formatting a formula result does not change how the formula works. It only changes how the result displays. If you subtract one date from another to find the number of days between them, the result will be a number, not a date, so you would format it as a number instead.

Frequently Asked Questions

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

No. The date itself stays the same. Only the way it appears on screen changes. If a cell contains January 15, 2024, and you change the format from "January 15, 2024" to "1/15/24", the date is still January 15, 2024 — it just looks different. Any formulas that use that cell will still work the same way.

Why does my date still look wrong after I format it?

Excel may not recognize what you typed as a date. If you entered it as text (like typing an apostrophe before the date), formatting will not work. Delete the entry and re-enter it without any extra characters, using a standard format like 1/15/2024 or 2024-01-15. Then format it as usual.

Can I format dates differently in different cells of the same column?

Yes. Select only the cells you want to change, then explore the format. You can have one cell show "1/15/2024" and another in the same column show "January 15, 2024" if you format them separately.

What if I want dates to show the day of the week too?

Use a custom format. Open Format Cells, select Date, find the Type or Format Code field, and enter a code like dddd, mmmm d, yyyy to get "Monday, January 15, 2024". The preview will show you the result before you explore it.