How to Change the Date Format in Excel

To change the date format in Excel, you change the cell's number format: select the cells, open Format Cells and pick a date style or type your own. The date itself doesn't change, only how it looks. This guide covers the built-in formats, custom codes, the TEXT function, and the most common reason a date format refuses to change.

How to change the date format with Format Cells#

Format Cells is the main way to change how dates look. Follow these steps:

1. Select the cells with dates.
2. Press Ctrl+1 on Windows or ⌘1 on Mac. You can also right-click and choose Format Cells.
3. On the Number tab, click Date.
4. Pick a format from the Type list. The Sample box shows a preview.
5. Click OK.

The Locale (location) box under the list changes which formats are offered. Choose English (United Kingdom) for day-first formats or English (United States) for month-first ones.

Formats at the top of the list with an asterisk (*) follow your computer's regional settings. A file using them can show 15/03/2025 on one computer and 3/15/2025 on another. Formats without an asterisk look the same everywhere.

For a faster option, use the Number Format box on the Home tab and choose Short Date or Long Date.

How to create a custom date format#

Choose Custom in Format Cells to build any layout you want. Type a code in the Type box using d for day, m for month and y for year:

The number of letters controls the style. d shows 5, dd shows 05, ddd shows Sat and dddd shows Saturday. The same pattern applies to months: m, mm, mmm and mmmm. Use yy for a two-digit year and yyyy for four digits.

The yyyy-mm-dd layout is a good choice for shared files, because nobody can confuse the day and the month. Microsoft's page on creating a custom number format lists every code.

How to change the date format with a formula#

Use TEXT when you need the formatted date as text, for example to join it to words or export it to another system. TEXT takes the date and a format code in quotes:

=TEXT(B2,"yyyy-mm-dd")

For 15 March 2025, the result is the text 2025-03-15. The codes are the same ones you'd type in a custom format.

The key difference: TEXT creates new text, while Format Cells keeps the real date. Text can't be used in date calculations or sorted reliably, so keep the original date column and add the TEXT column next to it. The TEXT function guide has more format codes for numbers and dates.

Why won't the date format change?#

If nothing happens when you apply a date format, the cell almost certainly holds text, not a date. Number formats only affect numbers, and real dates are numbers.

Check with any of these:

• The value sits on the left of the cell, while real dates sit on the right.
• =ISTEXT(A2) returns TRUE.
• Changing the format to General doesn't turn it into a number like 45731.

To fix it, convert the text to real dates first. For a column, use Data > Text to Columns, click Next twice, choose Date with the order your text uses, such as DMY, and click Finish. The full set of methods is in the guide to converting text to dates.

The opposite problem also happens: a real date shows as a number like 45731 because its format is General. That's covered in why a date shows as a number. The fix is simply to apply a date format.

How to swap between US and UK date formats#

If the dates are real, swapping order is just a new format. Apply dd/mm/yyyy for day-first or mm/dd/yyyy for month-first, and every date switches correctly.

If the dates were imported as text in the wrong order, a new format won't help. Worse, some may have been converted wrongly. For example, a US date 03/04/2025 (4 March) can be read on a UK computer as 3 April. Dates with a day above 12, like 03/15/2025, stay as text because there's no 15th month.

To fix a mixed column like this, go back to the original text and use Text to Columns with MDY, so Excel reads every value in US order.

How to change the default date format#

Excel's default short date comes from your computer's regional settings, not from Excel. To change it:

• Windows: open Settings > Time & language > Language & region, then Regional format, and change the short date format.
• Mac: open System Settings > General > Language & Region and change the Date format.

Restart Excel after the change. Dates formatted with an asterisk format then use the new default. Dates with a fixed custom format keep their format.

When the cell shows#

If a date shows as a row of # signs after you change its format, the column is too narrow for the new layout. Long formats like dddd, d mmmm yyyy need more space. Double-click the right edge of the column header to fit the width. A negative number with a date format also shows ####, because dates can't be negative.

Practise it#

Formatting dates correctly is a small detail that job tests and managers notice. Practise date basics in a live spreadsheet to understand how dates are stored, then practise number and date formatting to apply formats with confidence. Each lesson checks your work instantly. Once your dates look right, try adding days, months and years to them, or see more ideas in the overview of Excel date functions.