How to Change the Date Format in Excel

Change the date format by selecting your cells and using the Format Cells dialog

To change how a date appears in Excel, select the cells containing the dates, right-click, and choose Format Cells. In the dialog box that opens, 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 to explore the change. The dates themselves do not change—only how they display on your screen.

Excel stores dates as numbers behind the scenes. When you change the format, you are telling Excel how to show those numbers to you. A date stored as 45000 might display as "1/15/2023" in one format or "15-Jan-2023" in another, but the underlying value stays the same. This matters because formulas and calculations still work correctly no matter which format you choose.

Key Takeaways

Select the cells with dates, right-click, and choose Format Cells to open the formatting dialog.
Click the Number tab, select Date from the Category list, and choose your preferred format from the options shown.
Excel includes common formats like MM/DD/YYYY, DD/MM/YYYY, and text versions like "15-Jan-2023" that you can pick without typing anything.
If you do not see the exact format you want, you can create a custom format using codes like YYYY for year, MM for month, and DD for day.
Changing the format affects only how the date looks, not the actual date value or any calculations based on it.

Using the Format Cells dialog for built-in date formats

The fastest way to change a date format is through the Format Cells dialog. First, click on any cell containing a date you want to reformat. Then hold Shift and click on the last cell in the range you want to change, or drag to select multiple cells at once. If your dates are scattered, hold Ctrl and click each cell individually to select them all.

Right-click on your selection and click Format Cells. The dialog opens with several tabs at the top. Make sure you are on the Number tab. In the Category list on the left side, click Date. The middle column shows the date format code (like m/d/yyyy), and the right column shows a preview of how your dates will look. Scroll through the list to find the format you want, click it once to select it, then click OK.

Excel includes dozens of built-in formats. Common ones include 1/15/2023 (month/day/year), 15/1/2023 (day/month/year), 2023-01-15 (year-month-day), and 15-Jan-2023 (day-month-year with text). The preview column shows exactly what your dates will look like before you commit to the change.

Creating a custom date format when built-in options do not match

If none of the built-in formats match what you need, you can create your own. Open the Format Cells dialog the same way—select your cells, right-click, and choose Format Cells. Go to the Number tab and select Date from the Category list. At the bottom of the Category list, you will see Custom. Click it.

In the Type field at the top, you will see a code like m/d/yyyy or dd-mmm-yy. This code controls how the date displays. You can edit this code directly. Common codes are: YYYY for a four-digit year, YY for a two-digit year, MM for the month as a number, MMM for the month as three letters (Jan, Feb), MMMM for the full month name, DD for the day with a leading zero, and D for the day without a leading zero.

For example, to display dates as "January 15, 2023", type MMMM D, YYYY in the Type field. To display them as "15.01.2023", type DD.MM.YYYY. The preview shows what your dates will look like as you type. Once you have the format you want, click OK.

Changing date format using the Home ribbon

You can also access date formatting from the Home tab on the ribbon at the top of Excel. Select your cells with dates, then look for the Number group on the Home tab. You will see a dropdown that currently shows something like "General" or "Number". Click the dropdown arrow next to it and look for date formats in the list.

This method is faster if you only want to pick from common formats, but it gives you fewer options than the Format Cells dialog. If you do not see the format you want in the dropdown, click Format Cells at the bottom of the dropdown menu to open the full dialog with all available formats and the custom option.

Common mistakes when changing date formats

One frequent mistake is selecting a format that looks like text instead of a date. If your cells show dates as text (like the word "January" instead of "1"), Excel may not recognize them as actual dates. Text dates cannot be used in calculations or sorting. If this happens, you may need to convert the text to real dates first using a formula or the Text to Columns feature before reformatting.

Another mistake is forgetting that the format only changes the display, not the underlying value. If you have a formula that adds days to a date, changing the format will not affect the calculation—it only changes how the result looks. This is usually what you want, but it can be confusing if you expect the format change to affect how Excel treats the date.

A third issue is using a format that looks correct on your screen but causes problems when you share the file with someone in a different country. For example, 01/02/2023 means January 2nd in the United States but February 1st in Europe. If you work with people in different regions, using a format like 2023-01-02 (year-month-day) or 02-Jan-2023 (day-month-year with text) removes the ambiguity.

Formatting dates in a table or pivot table

If your dates are in an Excel table, select the date column by clicking the column header. Then right-click and choose Format Cells to open the dialog. The process is the same as formatting individual cells, but the format applies to the entire column at once. Any new dates you add to that column will automatically use the same format.

In a pivot table, right-click on any date in the pivot table and choose Format Cells. The dialog works the same way. However, if you refresh the pivot table later, the format may reset depending on your pivot table settings. To keep a custom format after refreshing, go to the Data tab, click Refresh All, and check that your format is still applied.

Frequently Asked Questions

Why do my dates show as numbers like 45000 instead of a date?

Excel stores dates as numbers, and the column is too narrow to display the full date, or the format is set to General instead of Date. First, try widening the column by double-clicking the border between column headers. If that does not work, select the cells, right-click, choose Format Cells, select Date from the Category list, and click OK.

Can I change the date format for just one cell?

Yes. Click the single cell, right-click, choose Format Cells, select Date, pick your format, and click OK. You can also select multiple non-adjacent cells by holding Ctrl and clicking each one, then format them all at once.

What if I want dates to show the day of the week, like "Monday, January 15, 2023"?

Open Format Cells, go to the Number tab, select Date, and look for a format that includes the day name in the list. If you do not see one, click Custom and type DDDD, MMMM D, YYYY in the Type field. DDDD gives you the full day name.

Does changing the date format affect formulas that use those dates?

No. Formulas work with the actual date value stored in the cell, not the format. Changing how a date looks on screen does not change how Excel uses it in calculations, sorting, or filtering.

How do I change the date format for an entire column at once?

Click the column header to select the whole column. Right-click and choose Format Cells. Select Date from the Category list, pick your format, and click OK. Every date in that column will use the new format, and any dates you add later will too.

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.