The fastest way to change a date format in Excel

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. Choose your preferred format from the list — Excel shows you a preview of how your dates will look. Click OK.

That single action changes how the dates display without altering the actual data underneath. A date stored as January 15, 2024 can appear as 1/15/2024, 15-Jan-24, 2024-01-15, or dozens of other formats depending on which one you pick.

If you need a format that does not appear in the built-in list, you can create a custom one using format codes. The same Format Cells dialog has a Custom category where you can type a code like dd/mm/yyyy or mmmm d, yyyy to get exactly what you want.

Key Takeaways

  • Select your date cells, press Ctrl+1 (Windows) or Command+1 (Mac), and choose a format from the Date category in the Format Cells dialog.
  • Changing the format only changes how dates look on screen — the underlying data and any calculations stay the same.
  • Excel includes dozens of built-in date formats covering most common needs, from short formats like 1/15/24 to long ones like January 15, 2024.
  • If no built-in format matches what you need, use the Custom category to build your own format code.
  • Format codes like dd/mm/yyyy, mmmm d, yyyy, and ddd, mmmm dd let you control exactly which parts of the date show and in what order.

Using the Format menu instead of right-click

If you prefer the menu bar, you can reach the same dialog through Home tab > Format button > Format Cells. This route works identically to the right-click method — you still land in the Number tab with Date selected, and you still see the same list of formats to choose from.

Some users find the menu route clearer because it is always in the same place, while others prefer right-click because it is faster. Both get you to the same place.

Understanding date format codes

Excel uses letter codes to build custom date formats. The most common ones are d (day), m (month), and y (year). The number of times you repeat each letter changes what you see: d gives you 1–31, while dd gives you 01–31 with a leading zero. m gives you 1–12, and mm gives you 01–12.

For months, mmm shows the three-letter abbreviation (Jan, Feb, Mar) and mmmm shows the full name (January, February, March). For years, yy shows the last two digits (24 for 2024) and yyyy shows all four (2024).

You can also add text and separators. The format dd/mm/yyyy produces 15/01/2024. The format mmmm d, yyyy produces January 15, 2024. The format ddd, mmmm dd produces Friday, January 15 (the three d's at the start show the day of the week).

Why dates sometimes show as numbers or errors

If your dates appear as long numbers like 45321 instead of a readable date, the column is too narrow or the format is set to General instead of Date. First, try widening the column by double-clicking the border between column headers — Excel will auto-fit the width. If the numbers persist, select the cells and explore a Date format as described above.

If you see ##### symbols, the column is definitely too narrow. Double-click the column border to auto-fit, or drag it wider manually. Once the column is wide enough, the date format will display correctly.

If a date shows as an error or text instead of a number, the cell may contain text that looks like a date rather than an actual date value. Excel stores real dates as numbers internally; text that resembles a date will not format as a date no matter which format you choose. You can check by clicking the cell and looking at the formula bar — a real date shows as a number, while text shows as text.

explore the same format to multiple columns at once

Select all the columns or cells you want to format together. You can click one cell, hold Shift, and click another to select a range. Or click a column header to select the entire column. Then open Format Cells (Ctrl+1 or Command+1) and choose your date format. The format applies to every selected cell at once.

If you want to copy a format from one cell to others without selecting them all together, use the Format Painter. Click the cell with the format you want to copy, click the Format Painter button in the Home tab (it looks like a paintbrush), then click or drag across the cells you want to explore it to.

Common date formats and when to use them

The format you choose depends on where your spreadsheet is going and who will read it. M/d/yyyy (1/15/2024) is standard in the United States. d/m/yyyy (15/1/2024) is standard in the UK, Australia, and much of Europe. yyyy-mm-dd (2024-01-15) is the international standard and sorts correctly when treated as text, making it useful for databases and data exchange.

For reports or documents meant for people to read, mmmm d, yyyy (January 15, 2024) or d mmmm yyyy (15 January 2024) are clearer and less likely to be misread. For internal spreadsheets where space is tight, m/d/yy (1/15/24) or d-mmm-yy (15-Jan-24) work well.

Frequently Asked Questions

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

No. Formatting only changes how the date appears on screen. The underlying value stays the same, and any formulas that use the date will still work correctly. If you change a date from 1/15/2024 to January 15, 2024, the data has not moved — only the display has changed.

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 some cells in a column show 1/15/24 and others show January 15, 2024 in the same column, though this usually makes a spreadsheet harder to read.

What if I type a date and Excel does not recognize it as a date?

Excel recognizes dates based on your system's regional settings. If you type a date in a format your computer does not expect, Excel may store it as text instead. Try typing the date in your system's standard format (for example, m/d/yyyy in the US), or use the Format Cells dialog to set the format before you type.

How do I show the day of the week along with the date?

Use a format code that includes ddd or dddd. The code ddd, mmmm dd, yyyy produces Friday, January 15, 2024. The code dddd, d mmmm yyyy produces the same thing in a different order. Three d's give the three-letter abbreviation (Fri), and four d's give the full name (Friday).

Can I create a format that shows the time along with the date?

Yes. Add time codes to your format code. h is hours, m is minutes, and s is seconds. The code m/d/yyyy h:mm AM/PM produces 1/15/2024 2:30 PM. Use hh:mm:ss for 24-hour time like 14:30:45. Be careful not to confuse m for minutes with m for months — Excel figures it out based on context.