The fastest way to change a date format
Select the cells containing the dates you want to reformat. Right-click and choose Format Cells, or press Ctrl+1 on Windows or Command+1 on Mac. In the Format Cells dialog, click the Number tab, then select Date from the Category list on the left. Choose the format you want from the list — Excel shows a preview of how your dates will look. Click OK.
That single action changes how the dates appear without altering the actual data underneath. The date January 15, 2024 can display as 1/15/2024, 15-Jan-24, 2024-01-15, or dozens of other formats depending on which one you pick.
If you need a format that does not appear in the standard list, you can create a custom one. In the same Format Cells dialog, select Date from the Category list, then look for a field labeled Type or Format Code at the bottom. You can type a custom code like dddd, mmmm d, yyyy to display dates as "Monday, January 15, 2024".
Key Takeaways
- Select your date cells, right-click, and choose Format Cells to open the formatting dialog in one step.
- Excel stores the actual date value separately from how it displays, so changing the format never changes your underlying data.
- The Date category in Format Cells shows dozens of built-in formats you can apply instantly without typing anything.
- Custom format codes let you create date displays that do not exist in the built-in list, using codes like mmm d, yyyy for "Jan 15, 2024".
- Format changes apply only to the cells you selected, so different columns can show dates in different formats in the same spreadsheet.
Using the Format menu instead of right-click
If right-clicking does not work or you prefer the menu bar, you can reach the same dialog through the ribbon. Click the Home tab, then look for the Number group. Click the small arrow icon in the bottom-right corner of that group — it opens the Format Cells dialog directly.
On older versions of Excel, the path is Format menu > Cells. The dialog that opens is identical, and the steps from there are the same.
Common date formats and what they look like
Excel's built-in Date category includes formats for nearly every region and use case. Here are the ones you will encounter most often:
| Format Name | How January 15, 2024 Appears | When to Use It |
|---|---|---|
| Short Date | 1/15/2024 | Spreadsheets, reports, most business documents |
| Long Date | Monday, January 15, 2024 | Formal letters, official documents, printed reports |
| ISO 8601 | 2024-01-15 | Data exports, databases, international documents |
| European | 15/01/2024 | Documents for European audiences |
| Month and Year Only | January 2024 | Financial reports, trend analysis |
When you select a format from the list, Excel shows you exactly how your dates will appear before you click OK. This preview prevents mistakes — you can see whether "1/15/2024" or "15/1/2024" is what you actually want.
What happens when Excel does not recognize text as a date
Sometimes you type what looks like a date, but Excel treats it as text instead. When this happens, the Format Cells dialog shows the text in the Category list, not Date, and changing the format does nothing.
The most common cause is typing a date in a format Excel does not expect. If your system is set to US English but you type "15/01/2024", Excel may store it as text because it does not match the expected pattern. To fix this, you can use the Data tab and select Text to Columns. Choose Delimited, click Next twice, then set the Column Data Format to Date and specify the format your dates are actually in. Click Finish, and Excel converts the text to real dates you can then reformat normally.
Formatting dates in a formula or new column
If you want to display a date in a specific format within a formula rather than reformatting cells directly, use the TEXT function. The syntax is =TEXT(cell_reference, "format_code"). For example, =TEXT(A1, "mmm d, yyyy") displays the date in cell A1 as "Jan 15, 2024" in a new cell.
This approach is useful when you need the original date in one column and a formatted version in another, or when you are combining a date with other text. The TEXT function does not change the original cell — it creates a new text value in the cell where you type the formula.
Common format codes for TEXT include mm/dd/yyyy for "01/15/2024", yyyy-mm-dd for "2024-01-15", and dddd, mmmm d, yyyy for "Monday, January 15, 2024". You can combine these codes with text — for example, =TEXT(A1, "mmmm d") produces "January 15" without the year.
Changing the default date format for your entire spreadsheet
If you want every new date you type to use a specific format, you can change Excel's default. On Windows, go to File > Options > Advanced, scroll down to the Editing Options section, and look for the date format setting. On Mac, go to Excel > Preferences > Edit.
Keep in mind that changing the default affects only new dates you type going forward — it does not reformat dates already in your spreadsheet. If you have an existing spreadsheet with dates in the wrong format, select all those cells and use the Format Cells method described at the top of this article.
Frequently Asked Questions
Why does my date show as a number like 45321 instead of a date?
Excel stores dates as numbers internally — 45321 represents January 15, 2024. The cell is formatted as a number instead of a date. Select the cell, right-click, choose Format Cells, select Date from the Category list, and click OK. The same number will now display as a date.
Can I format dates differently in different columns of the same spreadsheet?
Yes. Select only the cells in the first column you want to change, format them, then select the cells in the next column and format those separately. Each selection keeps its own format. You can have one column showing "1/15/2024" and another showing "January 15, 2024" in the same spreadsheet.
What if I copy a date to another spreadsheet and the format changes?
The date value itself stays the same, but the new spreadsheet applies its own default date format. Select the pasted cells, open Format Cells, and choose the format you want. If the format you need does not appear in the built-in list, create a custom format code in the Type field.
How do I create a format that shows only the month and year?
Open Format Cells, select Date, and look for a built-in format showing only the month and year — it usually appears as "January 2024" or "Jan 2024". If it is not there, select the Type field at the bottom and type mmmm yyyy for the full month name or mmm yyyy for the abbreviated version.
Can I undo a format change if I change my mind?
Yes. Press Ctrl+Z on Windows or Command+Z on Mac immediately after applying the format. If you have already closed the file, select the cells again, open Format Cells, and choose a different format or the original one you want.