Excel UK Date Format: How to Change Dates Correctly

To show dates in UK format in Excel for Microsoft 365 on Windows, select the date cells, press Ctrl+1, choose Custom, enter dd/mm/yyyy in the Type box, and select OK. This displays dates as day/month/year, such as 25/03/2026. If the cells contain text rather than real Excel dates, changing their number format will not convert them; use the conversion guidance below first.

Applying a UK format changes how a real date is displayed, not the date value stored in the workbook. That distinction matters when dates have been imported from another country or entered while your computer uses a different regional setting.

What You’ll Need

An Excel workbook with the dates you want to change. Save a copy first if the dates came from an import or are important to your work.
A way to tell whether the entries are real Excel dates or text. A real date usually behaves like a number: you can change its display format, sort it chronologically, or use it in date calculations.
For text dates, enough information to interpret the original order correctly. For example, 04/05/2026 could mean 4 May or April 5, so do not convert ambiguous entries without confirming their intended meaning.

Step-by-Step

Select the cells to format. In the worksheet, drag over the date cells, or click the column letter to select the whole column. If the dates are in separate areas, hold Ctrl while selecting each range on Windows. Avoid selecting unrelated numbers if you do not want them to receive a date format. To select the entire sheet, click the triangle where the row and column headings meet, but use that only when you want to format every applicable date cell.
Open Format Cells. Press Ctrl+1 on Windows. You can also right-click the selection and choose Format Cells. The dialog lets you set a display format without changing the underlying date value. On a Mac, select the cells and press Command+1 to open Format Cells; menu and keyboard details can vary slightly by Excel version.
Choose a UK date display. In the Format Cells dialog, open the Number tab and select Date. Look through the sample formats for a day-first pattern, such as 14/03/2012. If a Locale (location) dropdown is available, choose English (United Kingdom) and then select the date style you want. The preview shows how a selected date will appear. If the exact style you need is not listed, use the Custom option in the next step.
Set a specific format if needed. Select Custom in the Category list. Click in the Type box, replace its contents with dd/mm/yyyy, and click OK. This displays a two-digit day, two-digit month, and four-digit year, for example 07/11/2026 for 7 November 2026. Use d/m/yyyy instead if you prefer single-digit days and months without leading zeroes, such as 7/11/2026. The format codes use lowercase m for month in this date pattern.
Check the result in the worksheet. Confirm that a known date now appears in day/month/year order. For example, a cell containing 18 March 2026 should display as 18/03/2026. If a date such as 3 April displays as 04/03/2026 or otherwise seems to have changed meaning, stop and check the underlying value; a number format changes the appearance, not the stored date. Review several cells, especially entries with days of 12 or less, which can be ambiguous.
Apply the same format to more dates. If the dates are in another range, select that range and repeat the formatting steps. You can also copy a correctly formatted cell, select other date cells, then choose Home > Paste > Paste Special > Formats to copy its formatting without replacing the destination values. Check the selection carefully: Paste Special Formats can also copy other formatting, such as font, fill, and borders.
Format dates you enter later. To make future entries in a column display consistently, format the intended range or whole column before entering dates. This sets their display style, but does not change how Excel interprets what you type. Excel’s interpretation of newly typed dates can depend on regional settings, so enter dates unambiguously where possible, or use a date picker or a confirmed import process. A custom display format alone does not make Excel use UK input rules.

Excel for the web: Select the cells, then use Home > Number Format and choose a date option. If you need a particular day-first pattern, look for More Number Formats or the custom number format controls available in your version. The web interface may offer fewer locale and custom-format choices than desktop Excel. If the exact format is unavailable, open the workbook in the Windows or Mac desktop app and use Format Cells.

Common Mistakes

Assuming a display change fixes incorrect dates. If Excel has already interpreted 04/05/2026 as 5 April when you meant 4 May, applying dd/mm/yyyy cannot repair the stored value. It will only show that existing value in a different style. Verify the intended dates before changing them.
Trying to format text as dates. Imported values may look like dates but be stored as text. Selecting Date or entering a custom format will not change text characters into date values. Convert the text first, using a method that matches the original order.
Choosing a month/day/year pattern by mistake. Formats such as mm/dd/yyyy put the month first. For the usual UK day-first format, use dd/mm/yyyy. Check the preview and compare it with a date whose day is greater than 12; that makes the order easier to spot.
Confusing formatting with regional settings. A UK number format affects how selected dates are displayed. It does not change Windows, Mac, or Excel regional settings, nor does it necessarily control how new typed dates are interpreted.
Using an apostrophe to force a date to look right. Typing '25/03/2026 stores text, not a usable date. It may look correct but will not sort or calculate like a real date unless converted.

Tips

Choose the display precision you need. Use dd/mm/yyyy for a clear four-digit year, or dd/mm/yy when space is limited. Short two-digit years can be unclear in records that may be read years later, so four digits are usually safer.
Use a date format rather than typing spaces or punctuation into the value. A real date can be displayed in several styles while remaining suitable for sorting, filtering, and calculations. The cell’s formula bar may show a different representation from the grid; that does not necessarily mean the value changed.
Format a whole input range in advance. If a table will grow, format the date column before entering data. In an Excel table, new rows often inherit the formatting from the row above, but check the first few additions rather than assuming every paste will preserve it.
Keep source data when converting imports. If dates arrive in a file from another locale, retain an untouched copy of the original column until you have confirmed the converted dates. This gives you a reference if Excel interpreted an ambiguous value incorrectly.
Use four-digit years and consistent separators. Slashes are common in UK dates, but a format such as dd mmm yyyy displays 25 Mar 2026 and is especially useful in reports where a numeric date could be misread. This is a display choice; the underlying date remains the same.
Know when changing the system locale is unnecessary. If you only need one workbook or column to display UK dates, apply the cell format. Changing computer-wide regional settings can affect other programs and is not required for this task.

Troubleshooting

The dates do not change after I apply the format. The cells may contain text. Select one and check whether it is left-aligned by default or whether changing its format has no effect; alignment is only a clue, not a definitive test. Try sorting the column or inspect the formula bar. If it is text, use Data > Text to Columns on a copy of the column: choose Delimited, select Next twice, choose Date: DMY in the column data format options, and select Finish. This tells Excel to read the text as day, month, year. Confirm the source really is in that order first. Text to Columns can replace the selected data, so preserve the original column until you have checked the results.

Some dates turn into unexpected days or months. The original values may have been interpreted using month/day/year order, or the source text may use another order. Do not keep applying number formats in the hope that the stored date will correct itself. Compare a sample against the original data, and reconvert the text with the correct order if necessary. For ambiguous values such as 04/05/2026, consult the source or whoever provided the data; the digits alone cannot establish which date was intended.

The Type box rejects the format or the result looks odd. In Format Cells > Custom, enter dd/mm/yyyy without quotation marks, spaces, or extra characters. If your Excel uses a different language or regional configuration, the list of available formats or separators may differ. Select the closest UK day-first option under Date, or use a desktop version that provides the custom format controls. Also check that the selected cells are not showing a formula result or text imported with leading apostrophes.

New dates still appear month-first. Recheck that the new cells actually have the UK display format; pasted data can bring formatting from its source. If the cells are correctly formatted but the actual date is wrong, Excel may have interpreted the typed input according to another regional setting. Correct the value using a confirmed day-month-year entry method, then apply the desired display format. Formatting does not rewrite the date Excel already stored.

Only some cells change. Check whether the selected range included all relevant cells and whether some entries are text while others are real dates. Also look for merged cells, protected sheets, or conditional formatting that may make the display confusing. Select a single problem cell and open Format Cells to inspect its current category and sample; compare it with a cell that displays correctly.

Frequently Asked Questions

What is the standard UK date format in Excel?
The usual numeric format is day/month/year, written as dd/mm/yyyy. For example, 5 September 2026 appears as 05/09/2026. A regional or workplace convention may differ, so follow the format required for your file.

Will changing the format change the actual date?
No. Applying a number format changes how a real Excel date is displayed. The stored date value stays the same, so calculations and sorting continue to use that value. If Excel interpreted the original entry incorrectly, you need to correct the value itself.

How do I set UK dates as the default for every workbook?
There is no single cell-format setting in a workbook that automatically changes every date in every file. You can create a workbook template with the date columns formatted as UK dates, or adjust operating-system regional settings if you need broader changes. Be aware that system settings can affect date entry and other applications, not just the appearance of one spreadsheet.

Can I convert dates imported as text?
Yes, provided you know the text’s original order. For day-month-year text, use Data > Text to Columns and select Date: DMY before finishing. Work on a copy first and check ambiguous entries against the source. Formatting alone will not convert text into dates.

Why does Excel show a number instead of a date?
Excel stores real dates as serial numbers, and the cell may currently use General or Number formatting. Select it, open Format Cells, and choose a UK date format such as dd/mm/yyyy. If the number does not represent the expected date after formatting, verify the value and the workbook’s date system before changing anything.

Does this work in Excel for Mac?
Yes. The same custom date code, dd/mm/yyyy, is generally used. Select the cells and press Command+1 to open Format Cells, then choose Date or Custom. The dialog’s labels or available locale choices may vary by version.

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.