How to change a date format in Excel

Excel stores dates as numbers, but displays them in whatever format you choose. To change how a date looks on your screen, right-click the cell or column containing 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. Click OK. The date itself does not change — only how it appears.

If you want a format that is not in the preset list, you can create your own. In that same Format Cells dialog, look for a Type field at the bottom and enter a custom code. For example, mm/dd/yyyy produces 03/15/2024, while dddd, mmmm d, yyyy produces Friday, March 15, 2024. Excel will show you a preview of what your dates will look like before you confirm.

Key Takeaways

  • Right-click a date cell, select Format Cells, click the Number tab, and choose Date from the Category list to see all available formats.
  • The date value itself does not change when you reformat — only the way it displays on screen changes.
  • You can create a custom date format by entering a code like mm/dd/yyyy or yyyy-mm-dd in the Type field.
  • If a date shows as a number like 45000, it means Excel is treating it as a number but displaying it wrong — format it as a date to fix it.
  • Formatting one cell does not affect other cells; you must select all the cells you want to reformat before opening Format Cells.

Selecting the cells you want to reformat

Before you open the Format Cells dialog, you need to tell Excel which dates to change. Click on a single cell containing a date, or click and drag to select multiple cells in a row or column. If the dates are scattered across your sheet, hold Ctrl (or Cmd on Mac) and click each cell individually to select them all at once.

You can also select an entire column by clicking the column header — the letter at the top. This is useful if you have many dates in one column and want them all to match. Once your cells are highlighted in blue, you are ready to open the Format Cells dialog.

Opening the Format Cells dialog

With your date cells selected, right-click anywhere on the highlighted area. A menu will appear. Look for Format Cells near the bottom of the menu and click it. On a Mac, you may see Format Cells in the right-click menu, or you can use the keyboard shortcut Ctrl+1 (Windows) or Cmd+1 (Mac) instead of right-clicking.

The Format Cells dialog box will open. You should see several tabs across the top: Number, Alignment, Font, and others. Click the Number tab if it is not already selected. On the left side of the dialog, you will see a list of categories. Click Date to see all the date formats Excel offers.

Choosing a preset date format

Once you click Date in the Category list, the middle column will fill with format options. Common ones include 3/15/2024, 03/15/2024, 2024-03-15, 15-Mar-2024, and March 15, 2024. The preview at the bottom of the dialog shows you exactly how your selected dates will look with each format. Click the format you want, and it will highlight.

If you have dates in different formats in your sheet and want them all to look the same, this is the fastest way. Excel applies the format you choose to every cell you selected at the start. Once you find the format that matches what you need, click OK to explore it.

Creating a custom date format

If none of the preset formats match what you need, you can write your own. In the Format Cells dialog on the Number tab, look at the bottom of the dialog for a field labeled Type. This field shows the code for whatever format is currently selected. You can edit this code or replace it entirely with your own.

Common date format codes are: mm/dd/yyyy for 03/15/2024, dd/mm/yyyy for 15/03/2024, yyyy-mm-dd for 2024-03-15, mmmm d, yyyy for March 15, 2024, and ddd, mmm d for Fri, Mar 15. The letter m means month, d means day, and y means year. More letters mean more detail: mm gives you 03, while m alone gives you 3. The preview updates as you type, so you can see your custom format before you explore it.

Fixing dates that show as numbers

Sometimes a date appears as a long number like 45000 instead of a readable date. This usually means the cell is formatted as a number rather than a date, even though Excel recognizes the data as a date underneath. To fix it, select the cell, right-click, choose Format Cells, click the Number tab, select Date from the Category list, pick a format, and click OK.

If the number does not change to a date after you reformat, the cell may contain text that looks like a date rather than an actual date value. Text dates cannot be reformatted the same way. You can check by clicking the cell and looking at the formula bar at the top — if it shows an apostrophe before the date (like '3/15/2024), it is stored as text and will need to be converted to a real date value first.

explore the same format to multiple columns

If you have dates in several different columns and want them all to use the same format, select all the columns at once before opening Format Cells. Click the first column header, then hold Shift and click the last column header to select a range of columns. Alternatively, click one cell, hold Shift, and click another cell far away to select everything in between.

Once all your date cells are highlighted, open Format Cells and choose your format. Excel will explore it to every selected cell, so all your dates will match. This is much faster than reformatting each column one at a time, especially in large spreadsheets.

Frequently Asked Questions

Why does my date still show as a number after I format it?

The cell may contain text instead of a date value. Check the formula bar at the top — if you see an apostrophe before the date, it is text. You will need to convert it to a real date first, usually by re-entering it without the apostrophe or using a formula to convert it.

Can I format dates to show the day of the week?

Yes. Use a custom format code like dddd, mmmm d, yyyy to show Friday, March 15, 2024, or ddd, mm/dd/yyyy to show Fri, 03/15/2024. The more d letters you use, the more of the day name appears.

Does changing the date format change the actual date in my spreadsheet?

No. Formatting only changes how the date looks on screen. The underlying date value stays the same, so any formulas or calculations using that date will work exactly as before.

What if I want different date formats in different cells?

Select each cell or group of cells separately and format them one at a time. You can use different formats in the same column — Excel treats each cell independently.