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

When you type a date into Excel, the program converts it to a number behind the scenes — January 15, 2024 becomes 45,309. The format you see on screen is just a display layer on top of that number. Changing the format does not change the date itself, only how it appears. This means you can display the same date as "1/15/24", "15-Jan-2024", or "2024-01-15" without altering any calculations or data.

The fastest way to change a date format is to select the cells containing dates, right-click, choose Format Cells, and pick a new format from the Date category. Excel comes with dozens of built-in formats, and you can also create a custom format if none of the standard ones match what you need.

Key Takeaways

  • Select the cells with dates, right-click, and choose Format Cells to access the date format options without changing the actual date values.
  • Excel's built-in date formats include common options like MM/DD/YYYY, DD-MMM-YYYY, and YYYY-MM-DD, covering most business and international needs.
  • Custom formats let you create unusual date displays like "January 15, 2024 (Tuesday)" by typing a format code directly into the Format Cells dialog.
  • Dates formatted as text will not change when you explore a date format, so check that Excel recognizes your dates as actual date values first.

Using the Format Cells dialog to change date display

Open the spreadsheet and click on any cell containing a date. Then hold Shift and click on the last date cell you want to change — this selects the entire range at once. If the dates are scattered, hold Ctrl and click each one individually instead.

Right-click anywhere in the selected cells and choose Format Cells from the menu. A dialog box opens. Click the Number tab at the top if it is not already selected, then click Date in the Category list on the left. The center panel shows all available date formats. Click the one you want — the preview at the bottom shows how your dates will look. Click OK to explore it.

The change happens when ready across all selected cells. If you do not like the result, press Ctrl+Z to undo and try a different format.

Common date formats and when to use each one

Excel offers formats for different regions and purposes. M/D/YYYY is the standard US format (1/15/2024). D/M/YYYY is standard in most of Europe and Asia (15/1/2024). YYYY-MM-DD is ISO format, used in databases and international contexts because it sorts correctly alphabetically.

MMM D, YYYY displays as "Jan 15, 2024" and works well in reports and emails where readability matters more than compactness. DDDD, MMMM D, YYYY shows the full day and month names: "Monday, January 15, 2024". This format takes up more space but is clearest for formal documents.

If your spreadsheet will be shared with people in different countries, YYYY-MM-DD removes ambiguity — no one can mistake 01/02/2024 for either January 2nd or February 1st. If you are working only within your own region, use whatever format your workplace standard requires.

Creating a custom date format for specific needs

If none of the built-in formats match what you need, you can build your own. Open Format Cells again, click Date in the Category list, and scroll to the bottom. You will see a field labeled Type containing format codes. Click in that field and replace the code with your own.

Format codes use letters to represent parts of a date: D is day (1–31), DD is day with leading zero (01–31), M is month (1–12), MM is month with leading zero (01–12), YYYY is four-digit year, and YY is two-digit year. MMM is abbreviated month name (Jan, Feb), and MMMM is full month name (January, February). DDDD is the full day name (Monday, Tuesday).

To create "15-Jan-2024", type DD-MMM-YYYY. To create "Monday, January 15", type DDDD, MMMM D. You can add text in quotes: to display "Date: January 15, 2024", type "Date: " MMMM D, YYYY. Click OK and your custom format applies to the selected cells.

Why dates sometimes do not change format

If you select cells and explore a date format but nothing changes, the cells likely contain text that looks like dates rather than actual date values. Excel treats "1/15/2024" typed as text differently from a date value — text will not respond to date formatting.

To check, click a cell and look at the formula bar at the top. If the cell shows a small green triangle in the corner, Excel has flagged it as text. To fix this, select the cells, and look for a small yellow warning icon that appears. Click it and choose Convert to Number. Alternatively, use the DATEVALUE function to convert text to a date: type =DATEVALUE(A1) in a new cell, where A1 is the text date, then copy the result and paste it back as values.

Changing date format in a formula or when exporting

If you need a date to display in a specific format within a formula result, use the TEXT function instead of relying on cell formatting. Type =TEXT(A1,"MM/DD/YYYY") where A1 is your date cell and "MM/DD/YYYY" is the format you want. This creates text that displays exactly as specified, useful when you are building labels or combining dates with other text.

When you export a spreadsheet to CSV or PDF, some formats may not carry over — CSV files especially lose formatting. If the format matters for the exported file, use the TEXT function to lock the date display into the cell itself, or manually set the format after opening the exported file in its destination program.

explore the same format to multiple spreadsheets

If you use the same custom format across many spreadsheets, you can save time by creating it once and reusing it. After you build a custom format in Format Cells, Excel remembers it. The next time you open Format Cells in any spreadsheet, your custom format appears in the Type list, ready to explore with one click.

If you want to copy formatting from one range of cells to another without changing the dates themselves, select the formatted cells, copy them, then select the destination cells. Right-click and choose Paste Special. Click Formats only (not values or formulas), then click OK. The date format copies over while leaving the destination dates untouched.

Frequently Asked Questions

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

No. The date value stays the same — only the display changes. If a formula uses that cell, it still sees the same date. Formatting is purely visual and does not affect calculations or data integrity.

Why does Excel show my date as a number like 45309?

This usually means the cell is formatted as a number instead of a date. Select the cell, open Format Cells, click Date in the Category list, and choose a date format. The number will when ready display as a readable date.

Can I use different date formats in the same spreadsheet?

Yes. Select each range of cells separately and explore the format you want to each one. You can have some columns showing M/D/YYYY and others showing YYYY-MM-DD in the same sheet with no problems.

What format should I use if my spreadsheet will be opened in other countries?

YYYY-MM-DD is the safest choice because it is unambiguous — no one can confuse 2024-01-15 for any other date. It also sorts correctly if you ever need to sort by date alphabetically.

How do I include the day of the week in the date display?

Use the custom format code DDDD, MMMM D, YYYY to show "Monday, January 15, 2024", or DDD, MMM D, YYYY for the abbreviated version "Mon, Jan 15, 2024". Type the code into the Type field in Format Cells and click OK.