UK Date Format in Google Sheets: Set Dates Correctly

To get UK dates in Google Sheets, set the spreadsheet locale to United Kingdom, then format the date cells as dd/mm/yyyy if you want a consistent day/month/year display. This changes how dates are interpreted and displayed in the sheet, but it does not automatically turn every text entry into a real date.

What You’ll Need

A Google account with permission to edit the spreadsheet.
A web browser with the spreadsheet open in Google Sheets.
A clear idea of whether you want UK settings for the whole spreadsheet or only a particular range of cells.

Step-by-Step

Open the correct spreadsheet. Go to Google Sheets in your browser and open the file you want to update. If the file is shared, check that you have editing permission. A spreadsheet owner or editor can change its locale and cell formats; a viewer will need to ask someone with edit access.
Open the spreadsheet settings. From the top menu, choose File > Settings. In the settings window, open the General tab if it is not already selected. These are settings for this spreadsheet, not a change to every spreadsheet in your Google account.
Set the locale to the United Kingdom. Find the Locale drop-down menu, choose United Kingdom, then select Save settings. Sheets may reload the file. The locale sets regional defaults for dates, numbers and currency across the spreadsheet, so check that the wider effects suit your file before changing it.
Select the date cells. Click a cell or drag across the range you want to format. To select a whole column, click its letter at the top of the sheet. For non-adjacent ranges, hold Ctrl while selecting on Windows or Command on Mac. Applying a format to a whole column can be convenient, but consider whether that column also contains notes or other values.
Apply a UK date format. Choose Format > Number > Date to apply the sheet’s standard date style. If you need an exact pattern, select Format > Number > Custom date and time. Choose a day/month/year option in the picker, or enter dd/mm/yyyy in the custom format field if it is available, then click Apply. For example, a real date for 7 March 2026 should display as 07/03/2026.
Check that the entries are real dates. Click one of the formatted cells and look at the formula bar. A real date is stored by Sheets as a date value, even though it is displayed as characters. Changing its number format changes its appearance, not the underlying date. If the cell still shows the original text or does not respond to formatting, it may be text rather than a date; use the troubleshooting section below before relying on it in calculations or sorting.
Test an unambiguous date. In an empty cell, enter a date with a day greater than 12, such as 25/03/2026, and press Enter. With the UK locale, Sheets should interpret this as 25 March 2026. Do not use a date such as 03/04/2026 as your only test: it could mean 3 April or 4 March depending on how it was interpreted.
Review the rest of the spreadsheet. Check dates in other tabs, date columns, charts and formulas that use dates. Changing the locale affects the spreadsheet’s regional defaults, while custom formats on individual cells may remain as they were. If only certain cells need a UK display, select those cells and apply the custom date format rather than changing the locale for the whole file.

The main difference between these two controls is scope. The locale establishes the spreadsheet’s regional conventions and can affect how new entries are parsed. A cell format controls how date values in selected cells look. For a consistent result, set the locale first and then apply the exact date format to the relevant range.

Common Mistakes

Assuming a format change fixes text. Applying a date format does not reliably convert a string such as 03/04/2026 into a date value. It may remain text, or it may already have been interpreted as a different date. Confirm the meaning of imported or typed values before changing them.
Confusing day and month in ambiguous dates. Dates where both numbers are 12 or lower can be read in either order. If the spreadsheet was set to a US locale when data was entered, a value intended as 3 April could have been stored as March 4. Changing the locale later cannot infer what the person intended.
Changing the whole spreadsheet for a small range. Locale settings apply to the spreadsheet, not just the selected cells. They can affect default date, number and currency conventions. If only one date column needs a particular appearance, use a custom format for that range.
Expecting the display format to change the date’s value. Showing a date as dd/mm/yyyy does not change the date itself or its order in time. Formatting is not the same as converting or correcting the underlying value.
Typing dates with punctuation or text that Sheets does not parse as expected. Entries with extra spaces, unusual separators or month names in another language may be treated as text. Use a clear date entry and confirm the cell is recognised as a date.

Tips

Use a four-digit year for clarity. The pattern dd/mm/yyyy avoids uncertainty about the century and produces consistent dates, such as 05/11/2026 for 5 November.
Keep data and display separate. A genuine date value can be sorted chronologically, used in date calculations and displayed in different formats. Avoid converting dates to plain text just to make them look a certain way, especially in schedules, reports or shared data.
Prefer clear dates when sharing or importing. If a file will be opened by people using different regional settings, a written month such as 5 Nov 2026 can be clearer in a document or note. For spreadsheet calculations, keep the underlying cell as a real date and set the display format.
Format the input range in advance. If people will add dates to a particular column, select the column or input range and apply the intended format before they enter data. This helps keep new values visually consistent, though the spreadsheet locale still affects how ambiguous typed dates are interpreted.
Remember that Sheets and Excel have separate settings. Changing a Google Sheets locale does not change how a downloaded Excel workbook behaves in Microsoft Excel. Excel’s regional and cell-format settings are managed separately, so check the file again after exporting or opening it there.

Troubleshooting

The date still displays month/day/year. Select the cells and open Format > Number > Custom date and time. Choose or enter a day-first pattern such as dd/mm/yyyy, then apply it. A custom cell format can override the default style suggested by the locale.

Changing the format has no effect. The entry may be stored as text. Check whether the cell is left-aligned when nearby dates are right-aligned, and inspect the formula bar for an apostrophe or unwanted spaces. These clues are not conclusive, but they can help identify text. Re-enter a confirmed date after setting the UK locale, or clean and convert the imported data using an appropriate method. Make a copy of the sheet first if the data is important.

A date appears to change to a different day or month. Stop before editing more cells. A change in display may reveal that the value was parsed under a different locale, but it may also show that the underlying date was interpreted incorrectly at entry. Compare the affected cells with a trusted source or the original file. Do not assume that changing the locale restores the intended meaning of ambiguous values.

The locale option is unavailable or will not save. Confirm that you are in the spreadsheet’s File > Settings window and have edit permission. In a managed work or school account, an administrator or shared-file restrictions may affect what you can change. Ask the owner to update the setting, or use a custom cell format if you only need to change the appearance and have permission to edit cells.

Dates are inconsistent in different parts of the file. Select each relevant range and apply the same custom format. Formats can differ between tabs or cells even when they belong to one spreadsheet. Also check whether some entries are text and others are date values; a uniform appearance alone does not guarantee uniform underlying data.

Frequently Asked Questions

What is the UK date format in Google Sheets?
UK dates commonly use day/month/year, such as 25/03/2026 for 25 March 2026. For an explicit four-digit-year display, use the custom pattern dd/mm/yyyy.

Will changing the locale update every Google Sheet I own?
No. The locale setting applies to the spreadsheet you change. Other spreadsheet files have their own settings and need to be updated separately.

Does changing the locale convert dates already in the sheet?
It changes regional defaults and can affect how values are interpreted or displayed, but it cannot determine the intended meaning of ambiguous text already entered. Check existing data carefully, especially dates with both day and month at 12 or below.

Can I use UK dates in just one column?
Yes. Select the column or range and choose Format > Number > Custom date and time, then apply a day-first pattern. This changes the display for those cells without changing the spreadsheet locale for everything else.

Why does Google Sheets show a date as a number?
Sheets stores genuine dates as serial values, and a cell accidentally set to a number format can reveal that value. Select the cell and choose Format > Number > Date, or apply a custom date format. If the entry is text, changing the number format alone may not convert it.

Can I type 03/04/2026 and be sure it means 3 April?
Not without knowing how the spreadsheet interprets it. That entry is ambiguous across regional conventions. Use a day greater than 12 to test the locale, or confirm the intended date with its source before relying on the value.

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.