How to Change the Date Format in Excel

The fastest way to change how dates look in Excel

To change a date format in Excel, select the cells containing 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 will display in your chosen format without changing the actual data underneath.

This method works the same way in Excel for Windows and Excel for Mac. The dates themselves stay the same — only how they appear on screen changes. If you want a format that is not in the preset list, you can create a custom one using the same dialog.

Key Takeaways

Select the cells with dates, right-click, and choose Format Cells to open the formatting dialog in one step.
The Date category in the Number tab shows dozens of preset formats, from MM/DD/YYYY to Month Day, Year and many others.
Changing the format does not change the date itself, only how it displays, so formulas and calculations stay accurate.
You can create a custom date format by typing a code like YYYY-MM-DD or MMMM DD, YYYY in the Type field if the presets do not match what you need.

Step-by-step: using the Format Cells dialog

Open your spreadsheet and locate the cells with dates you want to reformat. Click on the first cell, then hold Shift and click the last cell in the range to select them all at once. If the dates are scattered across the sheet, hold Ctrl (or Command on Mac) and click each cell individually.

Right-click anywhere in your selection. A menu will appear. Click Format Cells — it is usually near the bottom of the menu. The Format Cells dialog will open. Make sure you are on the Number tab (it is the first tab and usually opens by default).

In the Category list on the left side, click Date. The middle section will now show a list of date formats. Scroll through and click the one you want. The preview at the bottom of the dialog shows how your dates will look. Once you find the format you want, click OK.

Common date formats and when to use them

Excel offers formats for nearly every way dates are written. MM/DD/YYYY (like 03/15/2024) is the standard in the United States. DD/MM/YYYY (like 15/03/2024) is standard in most of Europe and many other countries. YYYY-MM-DD (like 2024-03-15) is used in technical work and databases because it sorts correctly alphabetically.

MMMM DD, YYYY (like March 15, 2024) is readable and formal, often used in reports and documents. MMM DD, YYYY (like Mar 15, 2024) is a shorter version. Some formats include the day of the week, like DDD, MMMM DD, YYYY (Friday, March 15, 2024). Choose based on where the spreadsheet will be used and who will read it.

Creating a custom date format

If none of the preset formats match what you need, you can build your own. Open the Format Cells dialog the same way — select cells, right-click, choose Format Cells, and go to the Number tab. Click Date in the Category list.

At the bottom of the dialog, you will see a field labeled Type. This field shows the code for the currently selected format. Click in this field and delete the existing code, then type your own. Use these codes: YYYY for a four-digit year, YY for two digits, MMMM for the full month name, MMM for a three-letter month, MM for the month as a number with a leading zero, M for the month without a leading zero, DD for the day with a leading zero, and D for the day without one.

For example, type DD-MMM-YYYY to get 15-Mar-2024, or MMMM D, YYYY to get March 15, 2024. Click OK when you are done. Your custom format will appear in the Type list for future use in this spreadsheet.

What to do if dates show as numbers or errors

Sometimes a date appears as a long number like 45375 instead of a date. This usually means the cell is formatted as General or Number instead of Date. Select the cell, right-click, choose Format Cells, click Date in the Category list, pick a format, and click OK. The number will convert to a readable date.

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

If you see an error like #VALUE!, the cell may contain text that looks like a date but is not recognized as one by Excel. Check that the cell contains an actual date value, not text. You may need to re-enter it or use a formula to convert it.

Changing date format using the ribbon (quick method)

If you only need to change between a few common formats, the ribbon offers a faster route. Select your date cells. On the Home tab, look for the Number Format dropdown (it shows the current format, like "General" or "Date"). Click the dropdown arrow next to it and choose a format from the list.

This method is quicker for switching between preset formats, but it gives you fewer options than the Format Cells dialog. If you need a specific format or want to create a custom one, use the Format Cells method instead.

Frequently Asked Questions

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

No. The date itself stays the same. Only the way it displays on screen changes. If you have a formula that uses the date, it will still work correctly. The underlying data is not affected.

Why do my dates look different on someone else's computer?

Excel sometimes uses your computer's regional settings to display dates. If your computer is set to US English, dates may show as MM/DD/YYYY by default, while a computer set to UK English might show DD/MM/YYYY. To may support dates look the same everywhere, use a custom format like YYYY-MM-DD or spell out the month name (MMMM DD, YYYY).

Can I format just part of a date, like only the year?

Not with standard date formatting. If you need to extract or display only the year, month, or day, use a formula instead. For example, =YEAR(A1) pulls just the year from the date in cell A1, and =TEXT(A1,"MMMM") shows only the month name.

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

In the Format Cells dialog, look for a date format that includes time, like MM/DD/YYYY HH:MM:SS. These appear in the Date category list. Select one and click OK. If your cells contain only dates with no time data, the time portion will show as 00:00:00 or 12:00 AM.

How do I undo a date format change?

Press Ctrl+Z (or Command+Z on Mac) when ready after making the change. If you have already made other edits, you can select the cells again, open Format Cells, and choose a different format.

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.