Change Date Format in Excel to DD/MM/YYYY

To display Excel dates as DD/MM/YYYY, select the date cells, press Ctrl+1, choose Number > Custom, enter dd/mm/yyyy in the Type box, and select OK. This changes how real dates look without changing their stored values. If the entries are text rather than dates, you will need to convert them first.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows. The steps also work in most recent desktop versions of Excel.
The cells or column containing the dates you want to format. If you are unsure whether they are real dates or text, check the troubleshooting section below.
If the data is important, consider saving a copy of the workbook before converting text or changing imported data.

Step-by-Step

Select the dates. Click the first date cell and drag across the range, or click the column letter to select an entire column. To select separate ranges, hold Ctrl while selecting each range. If the column contains a heading, it is usually safer to select only the date cells so you do not format the heading as a date. Applying a date format to blank cells is harmless, but it will not create dates in them.
Open Format Cells. Press Ctrl+1 on Windows. You can also use Home > Number Format dropdown > More Number Formats. The Number Format dropdown is the box in the Number group on the Home tab; its label may show General, Date, or another current format. In Excel for Mac, select the cells and press Command+1, or use Format > Cells. These routes open the Format Cells dialog.
Choose a custom format. In the Format Cells dialog, open the Number tab and select Custom in the Category list. Click the Type box, remove the existing code if needed, and enter dd/mm/yyyy. The two d characters request a two-digit day, the two m characters request a two-digit month, and the four y characters request a four-digit year. For example, a real date representing 7 September 2026 should display as 07/09/2026.
Apply and check the result. Select OK. The selected date cells should now display day first, then month, then year, with leading zeroes for single-digit days and months. Check a date whose day and month are different, such as 7 September, so you can confirm the display is 07/09/2026 rather than 09/07/2026. The underlying date value remains the same, so sorting and date calculations continue to work when the cells contain real Excel dates.
Format new dates consistently. If you will add dates to the same column, format the whole data column or a sufficiently large range before entering them. Dates entered into formatted cells generally display using the selected format. If you add new rows to an Excel Table, formatting often carries into the new rows, but check the result because pasted data can bring its own formatting with it.
Use Excel for the web if needed. Select the date cells, then open the Home tab’s Number Format menu and choose More Number Formats or the custom number format option available in your Excel for the web interface. Enter or select the day/month/year format and apply it. The web interface can differ slightly by account and update, so if you cannot find a custom format control, open the workbook in the desktop app and use Ctrl+1 on Windows.

About the slash: In many regional settings, the slash in a date format is treated as the system’s date separator. If Excel displays a different separator even though you entered dd/mm/yyyy, try the custom code dd\/mm\/yyyy to request literal forward slashes. If the custom code is not accepted in your Excel locale, use the regular code and check the result in the worksheet.

Common Mistakes

Changing the display but expecting the value to change. Number formatting only controls appearance. A date displayed as 07/09/2026 is still the same underlying date as before formatting; it has not been converted into a different day.
Formatting text as if it were a date. A cell containing text such as 7/9/2026 may stay unchanged when you apply a date format. Excel applies date number formats to stored date values, not to arbitrary text that merely resembles a date.
Confusing day and month. A date such as 04/05/2026 is ambiguous if you do not know the intended order. Confirm whether the source means 4 May or April 5 before converting it. Formatting cannot resolve an ambiguity that was already present when the value was entered.
Using the wrong format code. In Excel custom date codes, mm means a two-digit month in a date pattern, while yyyy specifies a four-digit year. Entering only one d or m may omit the leading zero for single-digit values.
Replacing dates with TEXT output. A formula that turns a date into a formatted text string may look right but will no longer behave like a date for sorting, filtering, or date arithmetic. Prefer cell formatting when you only need a different display.

Tips

Check the formula bar. Select a formatted date cell and look at the formula bar. Excel often shows the underlying date in a familiar form there, while the worksheet cell follows the custom format. This is a useful way to tell that the display changed without changing the date.
Use a clear sample before formatting a large range. Test the code on one known date, such as 7 September 2026. Once the result reads 07/09/2026, apply the same format to the rest of the date column.
Reuse the format. After formatting one cell, you can copy it and use Paste Special > Formats on another range. This copies the appearance without replacing the destination cells’ date values. Check the selected range first so you do not overwrite formulas or other data.
Keep dates as dates. Real Excel dates can be sorted chronologically and used in date calculations even when displayed as DD/MM/YYYY. If you are preparing data for another system, confirm whether it expects actual date values or text in a particular format; the receiving system’s requirements may differ.
Remember regional settings. Excel may show date separators or interpret newly typed dates according to your computer’s regional settings. The custom format controls the display of recognized dates, but it does not guarantee that an ambiguous entry was interpreted as day/month/year when it was first entered.

Troubleshooting

The cells do not change after applying the format. The entries may be text. Click one cell and inspect it in the formula bar; a left-aligned value can be a clue, although alignment alone is not conclusive because formatting can change alignment. Try changing the cell to General temporarily. If the displayed entry remains exactly the same text instead of showing a number, it may not be a stored date. For a column of text dates in day/month/year order, select the column and use Data > Text to Columns. Choose Delimited and select Next, leave the delimiter choices unchanged if there is no delimiter to split on, and select Next again. Under Column data format, choose Date: DMY, then select Finish. Review the converted dates before continuing, especially if the source contains ambiguous entries.

Excel shows a number such as 46271. Excel stores dates as serial numbers, so a number can appear if the cells are set to General or Number. Select the cells, open Format Cells, and apply the custom date format again. If the serial value is outside the dates you expected, check that the source values really are dates and that a conversion did not assign the wrong day or month.

The result shows #######. The column may be too narrow to show the full date. Widen it by dragging the right edge of the column header, or double-click that edge to AutoFit the column. If widening does not fix it, check whether the cell contains a negative date or an unexpected value; Excel may not display some negative date/time values as ordinary dates.

The date is still in month/day/year order. Confirm that you selected Custom and entered dd/mm/yyyy, rather than choosing a preset that follows a different regional convention. Check a date where day and month are not the same. If the value itself was interpreted incorrectly when entered, changing its format will not fix the underlying date. Undo the entry if possible, or convert the original text using the correct DMY option in Text to Columns after checking the source data.

The separator is not a forward slash. Excel may display the system’s regional date separator in place of the slash format character. Try the escaped custom code dd\/mm\/yyyy. If that does not work in your regional settings, check Windows or macOS date separator settings, or use a format Excel accepts and verify the displayed result. Changing operating-system settings affects more than this workbook, so avoid doing so just to alter one column unless you understand the wider effect.

Some cells change and others do not. The range may contain a mix of genuine dates, text dates, blanks, and other values. Apply the display format to the range, then identify the entries that remain unchanged. Convert only the text dates after confirming their order, and leave labels or non-date values alone.

Frequently Asked Questions

Does changing the format alter the actual date?
No. For genuine Excel dates, the format changes only how the stored value is displayed. It does not change the date used in calculations, sorting, or filtering.

Why does 07/09/2026 sometimes mean July 9?
The displayed order can depend on the format, but the stored date may already have been interpreted using a different regional convention. Check the original source and use an unambiguous date or a deliberate DMY conversion when importing text.

Can I apply DD/MM/YYYY to an entire column?
Yes. Select the column letter and apply the custom format. This formats blank cells too, which is usually fine. If the column has a heading or mixed content, applying the format only to the date range can make the sheet clearer.

Will this format work in Excel on Mac?
Yes. The custom date format works in Excel for Mac. Select the cells and press Command+1 to open Format Cells, then choose Number > Custom and enter the format. Menu wording can vary slightly between releases.

Can I make Excel always enter dates as day/month/year?
Formatting a column affects how recognized dates display, but how typed dates are interpreted can depend on regional settings. Use a clear entry method, such as selecting a date through a date picker where available, or enter dates in a way that removes ambiguity and verify the result before relying on it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.