Change date format by selecting cells and using the Format menu

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, then select Date from the Category list on the left. You will see a list of date formats — pick the one you want and click OK. The dates in your selected cells will now display in the new format.

This method works whether your dates are already formatted as dates or entered as text that Excel recognizes as a date. If Excel does not recognize your entries as dates, they will not appear in the Date category, and you will need to reformat them first.

Key Takeaways

  • Select the cells with dates, right-click, choose Format Cells, click the Number tab, select Date from the Category list, and pick your format.
  • Excel includes dozens of built-in date formats, from "1/15/2024" to "January 15, 2024" to "15-Jan-24" and many others.
  • If you do not see a 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 does not change the actual date value in the cell — it only changes how the date appears on screen.
  • If your dates appear as numbers like 45000, they are stored as dates but formatted as numbers; use the Date category to display them correctly.

Using the Format menu from the ribbon

If you prefer using the ribbon at the top of Excel, select your date cells and click the Home tab. In the Number group on the right side of the ribbon, you will see a dropdown arrow next to the word "General." Click that arrow to open a list of number format categories. Select Date from the list.

This opens a smaller menu showing the most common date formats. Click the one you want, and Excel applies it immediately. If none of these formats match what you need, click Format Cells at the bottom of that menu to open the full Format Cells dialog, where you have access to all available formats and the option to create a custom one.

Creating a custom date format

Excel's built-in formats cover most common needs, but if you need a format that is not in the list, you can build one yourself. Open the Format Cells dialog (right-click and select Format Cells, or use the ribbon method above), click the Number tab, and select Date from the Category list. At the bottom of the dialog, you will see a field labeled Type that shows the format code for the currently selected format.

Click in that field and edit the code to create your custom format. Common codes are: YYYY for a four-digit year, YY for a two-digit year, MM for the month as a number, MMM for a three-letter month abbreviation, MMMM for the full month name, DD for the day, and DDDD for the full day name. For example, entering MMMM DD, YYYY produces "January 15, 2024."

After you type your custom code, click OK. Excel saves this format and adds it to your Date category list so you can use it again on other cells without retyping the code.

Fixing dates that appear as numbers

Sometimes dates show up as long numbers like 45000 or 45123. This happens when Excel stores the date correctly but formats it as a number instead of a date. To fix this, select those cells, open Format Cells, click the Number tab, select Date from the Category list, choose a format, and click OK. The numbers will instantly convert to readable dates.

If the cells still show numbers after you apply a date format, the entries may not actually be dates — they might be text that looks like a number. Try selecting the cells and using the Data tab on the ribbon. Click Text to Columns, click Next twice, set the Column data format to Date, and click Finish. This converts text entries into actual dates that Excel recognizes.

Changing date format for an entire column

To format all dates in a column at once, click the column header (the letter at the top) to select the entire column. Then right-click, choose Format Cells, click the Number tab, select Date, pick your format, and click OK. Every date in that column will now display in the format you chose.

This approach is useful when you import data from another source and all the dates are in a format you do not want to keep. Selecting the whole column saves you from having to select individual cells or ranges, and any new dates you enter in that column will automatically use the same format.

Date formats that include time

If your cells contain both dates and times, you can format them to show both or just one. Open Format Cells, click the Number tab, and look for Date in the Category list. Scroll through the format options — you will see some that show only the date, some that show only the time, and some that show both together, like "1/15/2024 2:30 PM."

If you want to show the date and time but in a specific order or style, create a custom format using the codes above plus time codes: HH for hours, MM for minutes, and SS for seconds. For example, YYYY-MM-DD HH:MM:SS produces "2024-01-15 14:30:45." Remember that MM means different things depending on context — next to YYYY it means month, but next to HH it means minutes.

Frequently Asked Questions

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

No. Changing the format only changes how the date appears on screen. The actual date value stored in the cell stays the same. If you change a date from "1/15/2024" to "January 15, 2024," the underlying data is identical — only the display changes.

Why do some date formats not appear in my format list?

Excel's format list changes based on your computer's language and regional settings. If you need a specific format that is not in the list, create a custom format using the format codes. Custom formats work regardless of your regional settings.

Can I format dates that are entered as text?

Not directly. If dates are stored as text, the Date category will not recognize them. Use the Text to Columns tool (on the Data tab) to convert text dates to actual dates first, then apply your date format.

What if I want to show only the month and year, like "January 2024"?

Create a custom format using the code MMMM YYYY. Open Format Cells, select Date, click in the Type field, clear it, type that code, and click OK. Excel will display only the month name and year for all selected cells.

How do I undo a date format change?

Press Ctrl+Z (or Cmd+Z on Mac) immediately after making the change. If you have already made other changes, select the cells, open Format Cells, and choose a different format or set the category back to General to remove all formatting.