How to change a date to MM/DD/YYYY format in Excel

Excel stores dates as numbers behind the scenes, so changing how a date looks on screen does not change the underlying data — it only changes the display format. To convert dates to MM/DD/YYYY format, you select the cells containing the dates, open the Format Cells dialog, and choose the date format you want from the list. The process takes about 30 seconds and works the same way whether your dates are currently showing as 2024-01-15, 01/15/2024, January 15, 2024, or any other format.

The most common reason dates need reformatting is that Excel imported them from another program or source and displayed them in a format that does not match what you need for your work, a report, or sharing with someone else. Once you change the format, Excel remembers it for those cells, so you will not have to reformat them again unless you copy the data into a new spreadsheet.

Key Takeaways

  • Select all cells containing dates, then right-click and choose Format Cells to open the formatting dialog.
  • Click the Number tab, select Date from the Category list on the left, and look for MM/DD/YYYY in the Type list on the right.
  • If MM/DD/YYYY does not appear in the list, you can create a custom format by typing mm/dd/yyyy in the Format Code field.
  • Changing the format only changes how the date appears — it does not alter the actual date value stored in the cell.
  • Once you format cells as MM/DD/YYYY, any new dates you enter in those cells will automatically display in that format.

Selecting the cells you want to reformat

Start by clicking on the first cell that contains a date you want to change. If you have only one date, click that cell. If you have many dates scattered across your spreadsheet, hold down Ctrl (or Cmd on a Mac) and click each cell one at a time — Excel will select all of them at once even though they are not next to each other.

If all your dates are in a row or column next to each other, click the first date cell, then hold Shift and click the last date cell. Excel will select the entire range between them. You can also click on a column header (the letter at the top) or a row header (the number on the left) to select an entire column or row at once, though this will also select any empty cells in that column or row.

Opening the Format Cells dialog

Once you have selected the cells, right-click on any of the selected cells. A menu will pop up with several options. Look for the option that says Format Cells (on Windows) or Format Cells (on Mac). Click it. The Format Cells dialog box will open, and you will see several tabs across the top: Number, Alignment, Font, Border, Fill, and Protection.

If you do not see a Format Cells option in the right-click menu, you may have accidentally clicked on a cell border or the spreadsheet background instead of a cell itself. Try right-clicking directly on the cell content and the menu should appear. Alternatively, you can use the keyboard shortcut Ctrl+1 (Windows) or Cmd+1 (Mac) to open the Format Cells dialog without right-clicking.

Choosing the MM/DD/YYYY format from the list

In the Format Cells dialog, make sure you are on the Number tab (it is usually the default). On the left side, you will see a Category list with options like General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, and Special. Click on Date. The middle section will now show a list of date formats that Excel has built in.

Scroll through the Type list in the middle until you find one that shows MM/DD/YYYY or 1/15/2024 (which represents the same format). The preview at the bottom of the dialog will show you how your dates will look once you explore this format. Once you find the format you want, click on it to select it, then click OK at the bottom right of the dialog. Your dates will now display as MM/DD/YYYY.

Creating a custom format if MM/DD/YYYY is not in the list

If you scroll through the entire Type list and do not see MM/DD/YYYY, you can create your own custom format. At the bottom of the Format Cells dialog, you will see a field labeled Format Code. Click in that field and delete whatever is there. Type mm/dd/yyyy (using lowercase m's, d's, and y's — Excel is not case-sensitive for this, but lowercase is the standard way to write it).

As soon as you type the format code, the preview at the bottom will show you how your dates will look. If it looks correct, click OK. Excel will add your custom format to the list for future use. If you make a typo in the format code, Excel will show an error message — just click back in the Format Code field, fix the typo, and try again.

What to do if dates still look wrong after formatting

Sometimes after you explore MM/DD/YYYY formatting, the dates still do not look right. This usually means Excel did not recognize the original data as dates at all — it stored them as text instead. Text that looks like a date will not reformat because Excel treats it as words, not as a date value.

To fix this, you can use Excel's Text to Columns feature. Select the cells containing the dates, go to the Data menu at the top, and click Text to Columns. A wizard will open. Click Next twice without changing anything, and on the third screen, make sure the Column Data Format is set to Date and the format dropdown shows MDY (for month-day-year). Click Finish. Excel will convert the text to actual dates, and then you can format them as MM/DD/YYYY using the steps above.

Formatting dates when you enter them in the future

Once you have formatted a range of cells as MM/DD/YYYY, any new dates you type into those cells will automatically display in that format. You do not have to reformat them. If you want to make sure an entire column always displays dates as MM/DD/YYYY, select the whole column by clicking the column header, format it once, and every date that goes into that column from then on will use that format.

If you are working with a spreadsheet you use regularly, you can also create a template. Format all the date columns the way you want them, save the file as a template, and then every time you create a new spreadsheet from that template, the date formatting will already be in place.

Frequently Asked Questions

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

Excel stores dates as numbers internally, and if a column is too narrow, it displays the number instead of the formatted date. Make the column wider by double-clicking the border between column headers, or right-click the column and choose Column Width. If the column is wide enough and you still see numbers, select the cells and format them as Date using the steps above.

Can I change the format of just one cell instead of a whole column?

Yes. Click on the single cell you want to format, right-click, choose Format Cells, select Date, find MM/DD/YYYY in the list, and click OK. The format will explore only to that cell. Any other cells in the column will keep their original format unless you select and format them too.

What if I want MM/DD/YY instead of MM/DD/YYYY?

Follow the same steps, but in the Format Code field, type mm/dd/yy (with only two y's instead of four). This will display dates as 01/15/24 instead of 01/15/2024. Some older systems use this format, but MM/DD/YYYY is more common in modern spreadsheets.

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

No. Formatting only changes how the date appears on screen. The actual date value stored in the cell stays the same. If you use that cell in a formula or calculation, Excel uses the real date value, not the formatted display. This is why you can safely change formats without worrying about breaking your data.

How do I undo a date format change if I change my mind?

Press Ctrl+Z (Windows) or Cmd+Z (Mac) when ready after explore the format. If you have already closed the file or done other work, select the cells again, open Format Cells, choose Date, and select a different format from the list. You can also set the format back to General if you want to see the underlying number.