Google Sheets UK Date Format: Change Dates Correctly

To use UK dates in Google Sheets, open the spreadsheet in a browser and choose File > Settings, set Locale to United Kingdom, then select Save settings. For a UK-style display such as 31/12/2026 without changing the spreadsheet’s locale, select the date cells and use Format > Number > Custom date and time to arrange the day, month, and year in that order.

Choose between changing the locale and changing the display

These two approaches solve related but different problems. A spreadsheet’s locale sets regional defaults for the whole file, including how dates and numbers are interpreted and displayed. A custom date format changes the appearance of selected cells; it does not change the spreadsheet’s regional settings or turn text into a date.

Use the United Kingdom locale when the sheet is mainly maintained for UK users, or when you want dates entered and interpreted according to UK conventions. Use a custom format when the spreadsheet needs to keep another locale, but particular cells should show day/month/year. For example, you might keep a file’s existing locale for its other regional conventions while displaying a report column as 31/12/2026.

It is also possible to use both: set the locale to United Kingdom, then apply a particular custom format to columns that need a consistent display, such as a four-digit year. The best choice depends on whether you need UK conventions throughout the spreadsheet or just a different date appearance in selected cells.

Set the spreadsheet locale to United Kingdom

Open the spreadsheet in Google Sheets in a web browser. You need permission to change its settings.
Choose File > Settings.
On the General tab, find Locale and choose United Kingdom.
Select Save settings. The spreadsheet may reload for the change to take effect.

The locale belongs to the spreadsheet, not just to your personal view. Changing it can affect how the file’s dates, numbers, and currency values are formatted, as well as how new entries are interpreted. Collaborators working in that file can therefore see the effects too. The change does not generally alter the language of the Google Sheets menus; interface language and spreadsheet locale are separate settings.

Before changing a shared or established workbook, consider whether its existing number and currency conventions should change as well. If only one date column needs a UK display, a custom format may be less disruptive. If the file is being set up for UK data entry, changing the locale before entering or importing dates can help avoid ambiguity.

Apply a UK date format to selected cells

To change only the display, select the date cells or entire column, then choose Format > Number > Custom date and time. In the format editor, arrange the day, month, and year components in the order you want. A common UK format is day/month/year, with a four-digit year, displayed as 31/12/2026. The editor shows a preview so you can check the result before applying it.

For a quick change, Format > Number > Date applies a date format based on the spreadsheet’s settings. If that does not produce the exact style you need, use the custom date and time editor. A format with a two-digit year is shorter, but a four-digit year is usually clearer in records that may be read or reused years later.

31/12/2026: day first, with a two-digit day and month and four-digit year.
31 Dec 2026: day first with a month abbreviation, which can make the month clearer to readers.
2026-12-31: year first, often useful for sorting and exchanging dates where a consistent, unambiguous representation is important.

Those examples are display choices, not three different dates. A custom format changes what a valid date value looks like in the cell; it does not change the underlying date.

Understand what Sheets stores as a date

A date that Google Sheets recognises is stored as a numeric date value, rather than merely as the characters shown on screen. Formatting tells Sheets how to display that value. For example, the same valid date can appear as 31/12/2026, 31 Dec 2026, or another chosen pattern without changing the day it represents.

This distinction matters when sorting, filtering, calculating deadlines, or making charts. Real date values can be ordered chronologically and used in date calculations. Text that only looks like a date may sort alphabetically and may not work as expected in calculations. Applying a date format to text does not by itself convert the text into a date value.

Time values can also be part of a cell’s numeric date value. A date-and-time format may show hours and minutes as well as the date. If a cell appears to contain an unexpected time, check whether the value includes a time component and whether the chosen format displays it. Changing to a date-only display can hide the time, but it does not necessarily remove it from the stored value.

Handle ambiguous dates and imported data

Dates such as 04/05/2026 are ambiguous when the reader does not know which convention was used: they could mean 4 May or April 5. A spreadsheet’s locale can influence how date-like text is interpreted, so changing the locale is not a safe way to resolve every ambiguous entry already in the file. Check the original source or ask the data provider which order was intended.

When entering new UK dates, set the spreadsheet to United Kingdom first and enter them consistently in day/month/year order. If a date has a day number above 12, such as 23/04/2026, it cannot be mistaken for a month/day date, but that does not make mixed conventions safe. For data exchanged between systems, an unambiguous year-month-day representation is often easier to verify; confirm that the receiving system recognises it as a date rather than text.

Imported CSV files deserve particular care. A CSV file may contain date-looking text with no information about whether it was intended as day/month/year or month/day/year. Review the source convention and the import result before relying on the values. If the import process offers an option to convert text to numbers, dates, and formulas, check how it affects the incoming dates rather than assuming it will infer the intended order correctly.

If values remain text, formatting alone will not fix them. A careful repair process is to keep an untouched copy of the source data, separate the date components if needed, and rebuild or re-enter the dates using the confirmed year, month, and day. Verify several results against the source, especially dates where both the day and month are 12 or lower.

Common problems and how to check them

The date still shows month first. Confirm that the correct cells are selected and check whether a cell-specific format is overriding the display you expected. Set a custom day/month/year format if needed.
The date does not change at all. The value may be text rather than a recognised date. Check whether it behaves like a date when sorted or used in a date calculation, and confirm the source format before converting it.
The day and month appear to have switched. Do not assume the display change has corrected the underlying meaning. Compare the value with the source record, especially for ambiguous dates, before editing or sharing it.
Numbers or currency look different after changing locale. Locale affects more than dates. Review number and currency columns and, if necessary, restore the original locale and apply a custom date format only to the relevant cells.
Different people see the same file differently. The spreadsheet locale is shared, but users may have different expectations about date conventions. Use clear headings or an unambiguous date display when collaborators work across regions.

A useful check is to select a few known dates, compare them with the source, and sort the date column from oldest to newest. If the order is not chronological, some entries may be text or may have been interpreted incorrectly. Check a copy first when the sheet contains important records; a display change is easy to make, but correcting mistaken date meanings requires evidence about the original data.

Sharing the file and using other spreadsheet apps

Google Sheets locale settings are saved with the spreadsheet, so a collaborator opening the same file can be affected by a locale change. A custom cell format is also part of the spreadsheet and can control how those selected cells appear. For a shared tracker, state the expected convention in a column heading or a short note, particularly if people enter dates from countries that commonly use different orders.

When downloading or exporting a spreadsheet, remember that the receiving application may display or interpret dates according to its own settings. Check an exported file in the target program before distributing it, especially if it will be opened as a CSV, which carries plain text rather than the spreadsheet’s cell-format rules. Microsoft Excel has its own regional and cell-format controls; its menus and behavior are not the Google Sheets paths described here.

For data that must travel between systems, consistent date values and an explicit convention are more dependable than relying on appearance alone. Keep a source copy, avoid mixing day/month/year and month/day/year in one column, and verify imported or exported samples at both ends.

Frequently Asked Questions

How do I change Google Sheets from US dates to UK dates?
Open File > Settings, choose United Kingdom under Locale on the General tab, and select Save settings. Check existing dates afterward, because ambiguous entries may need to be verified against their source.

How do I make dates show as dd/mm/yyyy in Google Sheets?
Select the relevant cells and choose Format > Number > Custom date and time. Arrange the day, month, and four-digit year in that order. This changes the display of recognised dates, not text values.

Will changing the locale change every date in the spreadsheet?
It changes the spreadsheet’s regional defaults, and can affect date and number presentation across the file. It does not mean every date’s underlying value has changed. Review other regional formats, including currency and decimal presentation, before keeping the new locale.

Why does changing the format not fix my date?
The entry may be text, or its day and month may have been interpreted incorrectly. A cell format cannot determine the intended meaning of ambiguous text. Check the original data and convert or re-enter the value only after confirming its components.

Can I use UK dates without changing the spreadsheet locale?
Yes. Apply a custom day/month/year format to the cells that need it. This is useful when you want a UK-style date display in part of a spreadsheet but do not want to change regional defaults for the whole file.

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.