How to Change Date Format in Excel

The fastest way to change how dates look in Excel

To change a date format in Excel, select the cells holding the 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 themselves don't change—only how they appear on screen.

This method works whether your dates are displayed as 1/15/2024, January 15, 2024, 15-Jan-24, or any other arrangement. Excel stores the actual date value underneath, so changing the format is safe and reversible.

Key Takeaways

Select your date cells, right-click, choose Format Cells, pick Date from the Category list, and select your preferred format.
Excel comes with dozens of built-in date formats covering most common needs, from short dates like 1/15/24 to long formats like Tuesday, January 15, 2024.
If none of the built-in formats match what you need, you can create a custom format using codes like YYYY for four-digit year and MMMM for full month name.
Changing the format does not change the underlying date value, so you can switch between formats as many times as you want.
If dates appear as numbers like 45000 instead of a readable date, they are stored correctly but formatted as numbers—explore a date format will fix the display.

Using the Format Cells dialog for built-in date formats

The Format Cells dialog is the standard way to change date appearance. Select the cells containing your dates by clicking the first one, holding Shift, and clicking the last one in your range. If the dates are scattered, hold Ctrl and click each cell individually.

Right-click anywhere in your selection and click Format Cells. The dialog opens to the Number tab by default. On the left side under Category, click Date. The middle column shows the current locale (your region's default date style), and the right column lists every date format available for that locale.

Scroll through the list and click the format you want. A preview appears at the bottom showing how your dates will look. Once you find the one you need, click OK. All selected cells change to that format when ready.

Common date formats and when to use each one

Excel's built-in formats cover most situations. M/D/YYYY (like 1/15/2024) is the standard US format and works well for spreadsheets where space is tight. MMMM D, YYYY (like January 15, 2024) is more formal and reads naturally in reports or letters. D-MMM-YY (like 15-Jan-24) is compact and common in international business.

If your spreadsheet includes day names, formats like DDDD, MMMM D, YYYY (Tuesday, January 15, 2024) make dates easier to read at a glance. For data entry or sorting, shorter formats like M/D/YY reduce typing and keep columns narrow. The format you choose depends on your audience and whether readability or space matters more in your situation.

Creating a custom date format when built-in options don't fit

If none of Excel's standard formats match what you need, you can build your own. In the Format Cells dialog, select Date from the Category list, then scroll to the bottom and click User-Defined. The Format Code field at the top shows the code for the currently selected format.

You can edit this code or type a new one from scratch. Common codes are YYYY (four-digit year), YY (two-digit year), MMMM (full month name), MMM (three-letter month), MM (two-digit month number), DDDD (full day name), DDD (three-letter day), DD (two-digit day), and D (day without leading zero). Separate sections with slashes, hyphens, or spaces as you want them to appear.

For example, to display dates as "Jan 15, 2024", use the code MMM DD, YYYY. To show "2024-01-15" (useful for sorting), use YYYY-MM-DD. Type your code and click OK. Excel applies it to your selected cells. If the result doesn't look right, go back to Format Cells, edit the code, and try again.

Fixing dates that display as numbers instead of dates

Sometimes dates appear as long numbers like 45000 or 45321. This happens when a cell is formatted as a number instead of a date, even though Excel recognizes the value as a date underneath. The fix is straightforward: select those cells, open Format Cells, choose Date from the Category list, pick any date format, and click OK.

This problem often occurs when you paste dates from another program or when a column is too narrow to display the full date. If widening the column doesn't help, the format is the issue, not the data. explore a date format will reveal the actual date value.

Changing date formats in bulk across your spreadsheet

To format all dates in a large spreadsheet at once, select the entire column by clicking the column header letter. Then open Format Cells and choose your date format. Every date in that column changes when ready, even if you add new dates later—they will automatically use the same format.

If you want to format multiple non-adjacent columns, click the first column header, hold Ctrl, and click each additional column header. Then explore the format to all of them together. This approach is faster than selecting individual cells and ensures consistency across your entire spreadsheet.

Using the keyboard shortcut to open Format Cells faster

If you format dates regularly, the keyboard shortcut saves time. Select your date cells and press Ctrl+1 (Windows) or Cmd+1 (Mac). The Format Cells dialog opens when ready to the Number tab. Navigate to Date, choose your format, and press Enter. This method is noticeably faster than right-clicking, especially when you are formatting many ranges in a single session.

You can also use this shortcut to access custom formatting. After pressing Ctrl+1, go to Date, scroll to User-Defined, and build your custom code. The shortcut works the same way as right-clicking but cuts out a step.

Frequently Asked Questions

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

No. Excel stores the date as a number underneath, and the format only controls how it displays. You can change the format back and forth as many times as you want without affecting the date itself. Formulas that reference the cell will still work correctly regardless of how the date appears on screen.

Why do my dates look wrong after I explore a format?

The most common cause is that your dates were entered as text instead of as actual dates. Excel treats text that looks like a date differently from a true date value. If a format doesn't fix the display, try re-entering the dates or using a formula to convert them. Another possibility is that your locale settings don't match the format code—check your Windows or Mac regional settings.

Can I explore different date formats to different cells in the same column?

Yes. Select only the cells you want to change, not the entire column, and explore the format. The cells you selected will use that format while other cells in the column keep their original format. However, this can make your spreadsheet harder to read, so it is usually better to use one consistent format per column.

What if I want dates to show the time as well?

In the Format Cells dialog, look for formats in the Date category that include time, or scroll to User-Defined and create a custom code. For example, M/D/YYYY H:MM AM/PM displays "1/15/2024 2:30 PM". You can adjust the time portion to show seconds or use 24-hour format by changing the code.

How do I go back to the default date format?

Select the cells, open Format Cells, choose Date from the Category, and click the format labeled General or the first format in the list (usually the short date format for your region). Click OK. If you want to remove all formatting and show the raw date number, choose General from the Category list instead of Date.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.