Excel stores dates as numbers, so changing how they look is separate from changing the actual data

When you type a date into Excel, the program converts it to a number behind the scenes — January 15, 2024 becomes 45,309. The way that number displays on your screen (01/15/2024 or 15-Jan-24 or January 15, 2024) is controlled by the cell format, not the data itself. Changing the format changes only the appearance, not the underlying value. This matters because formulas, sorting, and calculations all work on the actual number, not what you see.

The fastest way to change date format is to select the cells containing dates, right-click, choose Format Cells, 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. Click OK. The dates now display in your chosen format.

Key Takeaways

  • Right-click any cell or range of cells containing dates, select Format Cells, choose Date from the Category list, and pick your format from the options shown.
  • Excel comes with built-in date formats like MM/DD/YYYY, DD/MM/YYYY, and spelled-out formats like "January 15, 2024" — you do not have to create your own.
  • Changing the format changes only how the date looks on screen; the actual data and any formulas using that date remain unchanged.
  • If a date shows as a number like 45309 instead of a date, it is stored correctly but formatted as a number — use Format Cells to switch it to a Date category format.

The Format Cells dialog and where to find each date option

Open the Format Cells dialog by right-clicking any selected cell or range and choosing Format Cells from the menu. On Windows, you can also press Ctrl+1. On Mac, press Command+1. The dialog opens to the Number tab by default.

In the Category list on the left side, click Date. The middle column shows the Locale (your region or language setting) — leave this as is unless you need dates formatted for a different country. The right column lists all available date formats for your locale. Scroll through and click the one you want. A preview appears at the bottom showing how your dates will look. Once you find the format you need, click OK.

Common formats in the US locale include 1/15/24, 1/15/2024, 01/15/2024, 15-Jan, 15-Jan-24, 15-Jan-2024, January 15, 2024, and 15 January 2024. Other locales show different defaults — for example, many European regions default to DD/MM/YYYY instead of MM/DD/YYYY. If you do not see the exact format you want, you can create a custom one (see the Custom Format section below).

Creating a custom date format when built-in options do not match what you need

If none of the standard formats fit your needs, you can build your own. In the Format Cells dialog, select Date from the Category list, then scroll to the bottom of the format list and click User-Defined. A text field labeled Type appears, showing the code for the currently selected format.

Date format codes use letters to represent parts of the date: d for day (1–31), dd for day with leading zero (01–31), m for month (1–12), mm for month with leading zero (01–12), mmm for three-letter month (Jan, Feb), mmmm for full month name (January, February), yy for two-digit year (24), and yyyy for four-digit year (2024). Separators like slashes, hyphens, and spaces go between the codes exactly as you want them to appear.

For example, to display dates as 15-Jan-2024, type dd-mmm-yyyy. To display as January 15, 2024, type mmmm d, yyyy. To display as 2024/01/15, type yyyy/mm/dd. Type your code into the Type field and click OK. Your dates now display in that format.

Why dates sometimes show as numbers and how to fix it

If a date appears as 45309 instead of a date, the cell is formatted as a number rather than a date. This happens when you paste dates from another source, import data, or accidentally format a date cell as a number. The data itself is fine — Excel is just displaying it the wrong way.

To fix it, select the cells showing numbers, right-click, choose Format Cells, click Date in the Category list, pick a format, and click OK. The numbers convert to readable dates when ready. If the dates still look wrong after formatting (for example, showing 1904 instead of 2024), the source data may have been entered incorrectly, and you may need to re-enter those dates or check where they came from.

Formatting dates in a column without affecting other columns

To format only the dates you need, click the column header letter (A, B, C, and so on) to select the entire column, then right-click and choose Format Cells. This applies the format to every cell in that column. If you want to format only some cells in a column, click the first cell, hold Shift, and click the last cell you want to include. Then right-click and format as usual.

You can also select non-adjacent cells by clicking the first cell, holding Ctrl (or Command on Mac), and clicking each additional cell you want to format. Then right-click and format them all at once. This is useful when dates are scattered across different parts of your spreadsheet.

Date formats that work across different computers and programs

If you are sharing your spreadsheet with others, keep in mind that date formats can look different depending on the person's locale settings. A date formatted as 01/02/2024 might mean January 2 in the US but February 1 in Europe. To avoid confusion, use formats that spell out the month name (like 15-Jan-2024 or January 15, 2024) or use the ISO 8601 format (2024-01-15), which is the international standard and reads the same way everywhere.

If you are exporting your spreadsheet to another program like Google Sheets or a database, test the export first. Some programs recognize Excel date formats automatically, while others may convert them to numbers or text. Spelled-out month formats tend to transfer more reliably than numeric-only formats.

Frequently Asked Questions

Can I change the date format for just one cell without affecting the rest of the column?

Yes. Click the single cell, right-click, choose Format Cells, select Date, pick your format, and click OK. Only that cell changes. To format multiple non-adjacent cells at once, hold Ctrl (or Command on Mac) and click each cell, then right-click and format them together.

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

Use the User-Defined format code. In Format Cells, select Date, scroll to User-Defined, and type dddd, mmmm d, yyyy (four d's for the full day name). Click OK. The dddd code displays the complete weekday name; ddd would show the three-letter abbreviation like Mon.

Does changing the date format affect formulas that use those dates?

No. Formulas work on the actual date value stored in the cell, not on how it displays. If a formula adds days to a date or calculates the time between two dates, changing the format of those cells does not change the result. The formula still works correctly.

Why does my date show as 1/0/1900 after I format it?

This usually means the cell was not actually storing a date — it was storing text that looked like a date, or the original data was corrupted. Excel cannot convert text to a date just by changing the format. You may need to re-enter the date or use a formula to convert the text to a proper date value first.

Can I format times the same way as dates?

Yes. In Format Cells, select Time from the Category list instead of Date. You can also create a format that shows both date and time together using codes like mm/dd/yyyy hh:mm:ss (where hh is hours, mm is minutes, and ss is seconds). Select User-Defined to enter a custom date-time code.