How to change a date format in Excel

To change how a date displays in Excel, right-click the cell or range 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 on the right. Click OK. The date itself does not change — only how it appears on screen.

Excel stores dates as numbers behind the scenes. When you format a cell as a date, Excel just tells the cell how to display that number. This means you can show the same date as "1/15/2024", "January 15, 2024", "15-Jan-24", or dozens of other ways without altering the actual data.

If you are working with a large spreadsheet or need a format that Excel does not offer by default, you can create a custom format. The process takes a few extra steps but gives you complete control over how dates appear.

Key Takeaways

  • Right-click a date cell, select Format Cells, go to the Number tab, choose Date from the Category list, and pick your format from the options shown.
  • Changing the format changes only how the date looks on screen, not the date value itself or any calculations based on it.
  • If none of the built-in date formats match what you need, you can create a custom format using date codes like YYYY for year and DD for day.
  • Date formats vary by your computer's region settings, so the same format code may display differently depending on your location.
  • Dates that appear as numbers or show as "###" usually need to be formatted as dates or the column needs to be wider.

Using the Format Cells dialog for built-in formats

The Format Cells dialog is the standard way to change date formats and works the same way in Excel for Windows and Excel for Mac. Select the cell or range of cells with dates you want to reformat. You can click one cell, or click and drag to select multiple cells, or click the column header to select an entire column.

Right-click anywhere in your selection and click Format Cells. A dialog box opens. Click the Number tab at the top (it is usually the default tab). In the Category list on the left, click Date. The center panel now shows all available date formats for your region. Scroll through and click the one you want. The preview at the bottom shows how your date will look. Click OK to apply it.

Excel offers formats like "1/15/2024", "January 15, 2024", "15-Jan-24", and "2024-01-15". The exact list depends on your computer's region and language settings. If you do not see a format you like, you can create one.

Creating a custom date format

Custom formats let you display dates exactly the way you need them. Open the Format Cells dialog the same way — right-click your date cells and select Format Cells. Click the Number tab, then scroll to the bottom of the Category list and click Custom.

In the Type field at the top, you will see a code for the currently selected format. This code controls how the date displays. Common date codes are: YYYY for a four-digit year, YY for a two-digit year, MMMM for the full month name, MMM for a three-letter month abbreviation, MM for a two-digit month number, DDDD for the full day name, DD for a two-digit day number, and D for a one-digit day number. You can combine these in any order and add text between them.

For example, to display a date as "Monday, January 15, 2024", use the code DDDD, MMMM DD, YYYY. To display it as "15-Jan-2024", use DD-MMM-YYYY. Type your custom code into the Type field and click OK. Your new format appears in the Custom category for future use.

Fixing dates that display as numbers or ###

Sometimes dates appear as long numbers like "45321" instead of a recognizable date. This usually means the cell is formatted as a number rather than a date. The date value is correct — Excel just needs to be told to display it as a date. Right-click the cell, select Format Cells, click Date in the Category list, choose a format, and click OK. The number will convert to a date display.

If a date shows as "###" or "######", the column is too narrow to display the full formatted date. Double-click the border between the column headers to auto-fit the column width, or drag the border to the right to make the column wider manually. The date will then display normally.

Dates imported from other programs or websites sometimes arrive as text instead of actual dates. Excel will not format text that looks like a date. If formatting does not work, try using the Data menu and selecting Text to Columns, then step through the wizard without changing any settings — this often converts text dates to real dates that can be formatted.

How date formats affect sorting and calculations

Changing a date format does not change how Excel sorts or calculates with that date. If you sort a column of dates, Excel sorts by the actual date value, not by how it appears on screen. A date formatted as "15-Jan-2024" will sort correctly even though it looks different from "2024-01-15".

The same is true for calculations. If you use a date in a formula — for example, to calculate the number of days between two dates — the format makes no difference. Excel uses the underlying date value, not the display format. This is why you can safely change how dates look without worrying about breaking your spreadsheet.

Understanding regional date format differences

Excel's date formats depend on your computer's region and language settings. A format that displays as "1/15/2024" in the United States might display as "15/1/2024" in the United Kingdom or "2024-01-15" in Japan. The same format code produces different results in different regions.

If you are sharing a spreadsheet with someone in another country, be aware that they may see dates formatted differently than you do, even though you both have the same format applied. To avoid confusion, use a format that is unambiguous — for example, "15-Jan-2024" or "2024-01-15" — rather than relying on month and day numbers alone.

If you need a date format that does not match your region's standard, create a custom format using the codes described above. Custom formats override regional settings and display the same way for everyone.

Frequently Asked Questions

Will changing the date format affect my formulas or calculations?

No. Changing the format changes only how the date appears on screen. Excel still stores and uses the actual date value for any calculations or sorting. A formula that adds days to a date will work the same whether the date is formatted as "1/15/2024" or "January 15, 2024".

Can I apply a date format to cells that contain text instead of dates?

No. Excel can only format actual dates, not text that looks like a date. If you have text dates, use the Data menu and select Text to Columns to convert them to real dates first. Then you can format them.

What do I do if my custom date format is not showing up?

Custom formats are saved in the file where you created them, not globally on your computer. If you open a different file, your custom format will not appear in the Custom category. You can recreate it in the new file by entering the same code in the Type field. If the format is not applying at all, make sure the cells contain actual dates, not text.

How do I show only the month and year, without the day?

Create a custom format using the code MMMM YYYY for "January 2024" or MM/YYYY for "01/2024". Open Format Cells, click Custom, enter the code in the Type field, and click OK.

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

Yes. Use the code DDDD for the full day name (Monday, Tuesday, etc.) or DDD for a three-letter abbreviation (Mon, Tue, etc.). Combine it with other codes — for example, DDDD, MMMM DD, YYYY produces "Monday, January 15, 2024".