Excel stores dates as numbers, so changing how they look on screen takes just a few clicks

When you type a date into Excel, the program converts it to a number behind the scenes. That number stays the same whether you see it as "12/15/2024", "15-Dec-2024", or "2024-12-15". Changing the format means telling Excel which way you want to display that same date. You do this through the Format Cells dialog, which takes about 30 seconds once you know where to find it.

The method works the same in Excel on Windows, Mac, and in the web version. The steps below use the Windows desktop version, but the buttons and menus are in the same places everywhere.

Key Takeaways

  • Select the cells holding the dates you want to reformat, then right-click and choose Format Cells to open the formatting dialog.
  • Click the Number tab, select Date from the Category list on the left, and pick the format you want from the list on the right.
  • Excel comes with dozens of built-in date formats, including international formats like DD/MM/YYYY and YYYY-MM-DD.
  • If none of the built-in formats match what you need, you can create a custom format using a code like MMMM D, YYYY to display "December 15, 2024".

Select the cells you want to change

Click on the first cell that holds a date. If you have multiple dates to reformat, hold Shift and click on the last date in the range, or click and drag to select all the dates at once. You can also select non-adjacent cells by holding Ctrl (or Command on Mac) and clicking each one individually.

If you want to reformat an entire column of dates, click the column header letter at the top — for example, click "A" to select the whole column A. This is faster than selecting each cell one by one, and it will not hurt anything if the column also contains text or empty cells.

Open the Format Cells dialog

Right-click on your selection. A menu will appear. Click Format Cells at the bottom of that menu. On Mac, the option may say Format Cells or Format Cells... depending on your Excel version.

Alternatively, you can use the keyboard shortcut: press Ctrl+1 on Windows or Command+1 on Mac. This opens the Format Cells dialog without needing to right-click.

Choose Date from the Category list

The Format Cells dialog opens with several tabs at the top. Make sure you are on the Number tab — it is usually the first one. On the left side of the dialog, you will see a list of categories: General, Number, Currency, Accounting, Date, Time, Percentage, and so on.

Click on Date in that list. The right side of the dialog will now show all the date formats Excel has available. Each format shows a preview of what your date will look like.

Pick the format you want and click OK

Scroll through the list on the right until you find the format that matches what you need. Common formats include:

  • 12/15/2024 (month/day/year with slashes)
  • 15-Dec-2024 (day-month abbreviation-year with hyphens)
  • 2024-12-15 (year-month-day, often used in databases)
  • December 15, 2024 (full month name, day, year)
  • 15/12/2024 (day/month/year, common outside the US)

Click on the format you want. You will see a preview at the top of the dialog showing how your dates will appear. Once you are happy with the choice, click OK. Excel will explore that format to all the cells you selected.

Create a custom date format if you need something different

If none of the built-in formats match what you need, you can make your own. In the Format Cells dialog, scroll to the bottom of the Category list and click Custom. You will see a text box labeled Type that contains a code for the currently selected format.

Delete what is in that box and type a custom code. The most common codes are:

  • M = month as a number (1, 2, 3)
  • MM = month with a leading zero (01, 02, 03)
  • MMM = month abbreviation (Jan, Feb, Mar)
  • MMMM = full month name (January, February, March)
  • D = day as a number (1, 2, 3)
  • DD = day with a leading zero (01, 02, 03)
  • YYYY = four-digit year (2024)
  • YY = two-digit year (24)

To display "December 15, 2024", you would type: MMMM D, YYYY. To display "15-Dec-2024", you would type: DD-MMM-YYYY. The preview at the top updates as you type, so you can see exactly what your dates will look like. Click OK when you are done.

What to do if dates show as numbers or hashes

Sometimes after you change the format, you see a column full of numbers like 45000 or a row of hash marks (####) instead of dates. The hash marks mean the column is too narrow to display the formatted date. Double-click the border between the column headers to auto-fit the column width.

If you see numbers instead of dates, the cells may not actually contain dates — they may contain text that looks like a date. Excel treats text and dates differently. You can check by clicking on a cell and looking at the formula bar at the top. If it shows a number like 45000, it is a real date and the format just needs adjustment. If it shows text like "12/15/2024", you will need to convert it to a real date first, which usually means re-entering it or using a conversion formula.

Frequently Asked Questions

Can I change the date format for just one cell instead of a whole column?

Yes. Click on the single cell, right-click, choose Format Cells, select Date, pick your format, and click OK. The change applies only to that cell. You can do this for as many individual cells as you need, but if you have many dates to reformat, selecting them all at once and formatting them together is faster.

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

Use a custom format. Open Format Cells, go to Custom, and type: DDDD, MMMM D, YYYY. The four D's tell Excel to spell out the full day name. If you want just the abbreviation (Mon, Tue, Wed), use DDD instead.

Will changing the date format affect my calculations or formulas?

No. Formatting only changes how the date looks on screen. The actual date value stays the same, so any formulas that use those dates will work exactly as before. You can format the same date ten different ways and all your calculations will remain correct.

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

Select the cells, right-click, choose Format Cells, select Date, and pick the format labeled "General" or the one that matches your system's default date format. Click OK. If you are not sure which one is default, look at other dates in your spreadsheet that you have not changed yet.