The fastest way to change a date format in Excel

Select the cells containing the dates you want to change. 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 is the standard path for most date changes. If you need a format that is not in the preset list, you can create a custom format using date codes like YYYY for four-digit year, MM for month, and DD for day. The same Format Cells dialog has a Custom category where you can type your own format code.

Key Takeaways

  • Select your date cells, right-click, and choose Format Cells to open the formatting dialog.
  • The Number tab shows preset date formats organized by region and style — pick one and click OK.
  • If no preset matches what you need, use the Custom category to build a format code like MM/DD/YYYY or DD-MMM-YY.
  • Changing the format does not change the actual date value in the cell, only how it displays on screen.
  • You can also use the Format menu at the top instead of right-clicking if that is easier for your workflow.

Using the Format menu instead of right-click

If you prefer the menu bar, select your date cells and go to Format at the top, then click Cells. This opens the same Format Cells dialog as right-clicking. The Number tab is where you choose Date and then pick your format. This method works the same way on both Windows and Mac versions of Excel.

Some people find the menu route clearer because it is always in the same place. Others prefer right-click because it is faster. Both lead to the same result, so use whichever feels natural to you.

Common date formats and when to use them

Excel offers formats for different regions and purposes. M/D/YYYY is the standard US format (3/15/2024). D/M/YYYY is used in the UK and many other countries (15/3/2024). YYYY-MM-DD is the ISO standard, often used in databases and international work (2024-03-15). MMM D, YYYY spells out the month (Mar 15, 2024), which is clearer in documents meant for people to read.

If your spreadsheet will be shared across regions or imported into another system, check what format the other side expects before you change. A date that looks right to you might confuse someone in another country or cause an import to fail.

Creating a custom date format

Open Format Cells and go to the Custom category. You will see a list of existing custom formats and a text box labeled Type where you can enter your own code. Common codes are YYYY (four-digit year), YY (two-digit year), MM (month as number), MMM (month as three letters), MMMM (full month name), DD (day), and D (day without leading zero).

For example, to show dates as "Mar-15-2024", type MMM-DD-YYYY. To show "3/15/24", type M/D/YY. Excel updates the preview as you type, so you can see exactly how your dates will look before you click OK. If you make a mistake, just clear the box and try again.

Custom formats are saved in that workbook, so if you create one you like, you can reuse it on other cells in the same file. If you want to use it in a different workbook, you will need to recreate it or copy a cell with that format and paste it as format only into the new file.

Why your dates might not be changing

If you change the format and the dates still look the same, the cells might not actually contain dates — they might contain text that looks like dates. Excel treats text and dates differently. A text date will not respond to date formatting. To check, click a cell and look at the formula bar at the top. If it shows an apostrophe before the date (like '3/15/2024), it is text.

To convert text dates to real dates, you can use the Text to Columns feature. Select the cells, go to the Data menu, click Text to Columns, and follow the wizard. On the final step, make sure the column format is set to Date before you finish. This converts the text to actual date values that will respond to formatting.

Changing date format for an entire column

Click the column header letter to select the whole column at once. Then right-click and choose Format Cells, or use the Format menu. Pick your date format and click OK. Every date in that column will change to the new format. This is faster than selecting individual cells if you have many dates to reformat.

If your column has a mix of dates and other data (like headers or notes), select only the cells with dates instead of the whole column. That way you do not accidentally format text or numbers that should stay as they are.

Formatting dates in a pivot table or chart

Dates in pivot tables and charts follow the same process: select the date cells, open Format Cells, and choose your format. In a pivot table, right-click on a date in the data area and select Format Cells. In a chart, double-click the chart to enter edit mode, then right-click the date axis and choose Format Axis to change how dates display on that axis.

Charts sometimes have their own date settings separate from the cell format, so if changing the cell format does not affect the chart, try editing the axis directly. This is especially common with timeline slicers and date-based charts.

Frequently Asked Questions

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

No. Formatting only changes how the date appears on screen. The underlying value stays the same. If you change 3/15/2024 to display as "Mar 15, 2024", the cell still contains the same date and will calculate correctly in formulas. You can change the format back anytime without losing data.

What if I want dates to show the day of the week?

Use the Custom category and add the day code. DDDD, MMMM D, YYYY shows "Friday, March 15, 2024". DDD MM/DD/YY shows "Fri 03/15/24". The preview updates as you type, so you can see the result before you click OK.

Can I format dates differently in different cells of the same column?

Yes. Select only the cells you want to change, not the whole column. You can have one date show as "3/15/2024" and another in the same column show as "Mar 15, 2024" if you format them separately. This is useful when different rows need different display styles.

Why do some dates show as numbers like 45375 instead of a date?

Excel stores dates as numbers counting from a start date. If a cell shows a number instead of a date, it is formatted as a number rather than a date. Select the cell, open Format Cells, choose Date from the Category list, and click OK. The number will convert to a readable date.

How do I format dates that are in different formats in the same column?

First convert them all to actual dates using Text to Columns if needed. Then select the whole column and explore one date format. All dates will display in that format once they are recognized as date values. If some cells still show differently, they may be text and need conversion first.