A colleague in London types 03/04/2026 into a shared budget, meaning April 3rd. The sheet, still set to a US locale, files it as March 4th. Nobody notices for a week, and then a scheduled report ships with the wrong month totals. Google Sheets is not guessing at random — it is reading every cell through its locale — and the fix is almost never the Format menu. It is the setting that governs how the sheet interprets and displays every number and date across every tab.
Change the sheet’s locale
Locale is per-spreadsheet, not per-user and not per-tab. Open the file, then File → Settings. The Locale dropdown affects three things at once: the default date pattern, the default currency, and how new text entries are parsed as numbers. Google’s own help doc for locale and language spells out one detail worth pinning: changing the locale does not change your interface language — that lives in your Google Account, not the sheet.
- Open the sheet and choose File → Settings.
- Switch Locale to the region whose date order you want — United Kingdom for
DD/MM/YYYY, United States forMM/DD/YYYY. - Set Time zone in the same dialog if the sheet uses
NOW()orTODAY()anywhere. - Click Save settings. The sheet reloads; existing numeric dates reformat in place.
Locale is one-way for old cells
Any cell that already holds a real date value — the kind Sheets stores as a serial number — flips to the new pattern the moment you save. A cell that looks like a date but was pasted or imported as text stays exactly as typed, in the exact string it arrived as. That distinction is the source of most “the format won’t change” complaints.
What locale actually changes, and what it doesn’t
Every date in Google Sheets has two layers: the value (an integer counting days from December 30, 1899) and the display format applied on top of it. Locale rewrites the display format for every date-typed cell and rewrites the default parser for anything you type next. It does not touch text cells, and it does not touch cells whose format was manually overridden.
=ISNUMBER(A2) in an empty cell. TRUE means Sheets has a serial number underneath and the locale will reformat it. FALSE means the value is text and no format menu will move it.
The trickiest case is an ambiguous string like 01-02-2023. Under a US locale, Sheets reads that as January 2nd. Under a UK locale, February 1st. Same sheet, different interpretations depending on when the cell was entered. Flip the locale after the fact and the underlying serial number stays put — only the display shifts — so the number still means whatever the old locale decided it meant on the day it was typed.
The one-week import trap
Import a CSV that a European tool exported and you inherit its convention wholesale. If your sheet is set to US locale, rows where the day exceeds 12 (13/04/2026, 27/11/2025) get stored as text because they cannot be parsed as US dates. Rows where day and month are both ≤ 12 get silently misread. That is the row-count mismatch that shows up in a formula three days later.
Fix cells that stay stuck as text
Once you have identified a text-typed date column with ISNUMBER, the conversion path depends on how clean the strings are. DATEVALUE is the workhorse: it parses a string using the sheet’s current locale and returns the serial number, which you then format as a date.
=DATEVALUE(A2)Fails with #VALUE! when the string does not match the sheet’s locale pattern. That is the signal — not a bug — to fix the locale first, then re-run. For strings with stray whitespace from a CSV, wrap in TRIM:
=DATEVALUE(TRIM(A2))If the source uses a delimiter the locale does not accept (dot separators, ISO strings with a T in the middle), clean the string with REGEXREPLACE before parsing:
=DATEVALUE(REGEXREPLACE(A2, "\.", "/"))03.04.2026 (text, left-aligned)Sheets treats it as a string; sorting orders it lexicographically.
=DATEVALUE(REGEXREPLACE(A2,"\.","/"))Returns 46116; format the cell as a date and sorting works.
Paste the formula result over the original column with Paste special → Values only when you want to drop the source strings, then apply Format → Number → Date.
Custom-format one column without touching the sheet’s locale
Sometimes changing the entire sheet’s locale is wrong — the currency column is USD, but one date column needs to show ISO YYYY-MM-DD for a downstream tool. Format that column alone:
- Select the column.
- Format → Number → Custom date and time.
- Delete the tokens in the input at the top, then click Year, Month, Day from the picker in the order and separators you want.
- Save. The underlying serial numbers are unchanged; only these cells display differently.
Column-level formats survive a locale change. The sheet’s locale only overrides the default pattern — anything you explicitly set stays put.
The QUERY format shortcut
The custom-format dialog covers most needs but has to be applied to real ranges. When you need to render dates one way in an output view while keeping the source column untouched — a dashboard, a printable summary — the QUERY function’s format clause does it inline.
=QUERY(A1:C, "select A, B, C format B 'yyyy-mm-dd (ddd)'", 1)Column B keeps its stored format in the source range. The QUERY output displays it as 2026-04-03 (Fri). Change the string in the query and the display updates instantly, no menu clicking. The format tokens are the same set the custom-format dialog uses: d, dd, ddd, dddd for day; m, mm, mmm, mmmm for month; yy, yyyy for year.
IFERROR during dashboard builds. A single non-date value in the middle of the source column throws the whole format clause, and the sheet just shows #VALUE! with no useful message.
Timezone drift on shared sheets
The Time zone dropdown in File → Settings is easy to miss because it sits under Locale, but it decides what TODAY() and NOW() return. A sheet whose time zone is New York shows a New York date to a Sydney collaborator, even though the collaborator’s browser thinks it is already tomorrow.
NOW() cell before saving, and confirm nothing is off after.
For a truly locale-neutral sheet — one that ships to teams in three regions — pin dates to ISO format explicitly with a custom date column format, and use a currency-neutral column type (numbers without the currency prefix) instead of relying on locale. That way, no future locale flip breaks anyone’s read of the numbers.
A quick diagnostic map
When a date column looks wrong, run through these in order. Most fixes land at step 2.
| A | B | C | |
|---|---|---|---|
| 1 | Symptom | Diagnostic | Fix |
| 2 | Column stuck as text | =ISNUMBER(A2) → FALSE | =DATEVALUE(TRIM(A2)) |
| 3 | Day and month swapped | Compare locale to source | File → Settings → Locale |
| 4 | One column needs a different pattern | Rest of sheet is fine | Format → Number → Custom date |
| 5 | TODAY() off by a day | Time zone mismatch | File → Settings → Time zone |
If the column has never been a real date, no display setting will help. That is the whole shape of the problem: locale governs display and parsing, but conversion is a formula. To keep the input clean going forward, restrict the column to valid dates with data validation so future entries cannot land as text.
Where to start
Fix the sheet’s locale first — that alone repairs anything Sheets stored as a real date. Then use ISNUMBER to find the cells that stayed text and convert them with DATEVALUE. Reach for custom date formats only when one column needs to break from the rest of the sheet, and touch the time zone only after you have checked what depends on it.
- ✓ Set the sheet’s locale and time zone in File → Settings
- ✓ Check
=ISNUMBER(A2)before blaming the format menu - ✓ Convert stuck text with
DATEVALUE(TRIM(...)), then paste values - ✓ Override a single column with Custom date; leave the rest on locale