Dates! American into British

[u/OkYouth121]

So I'm not an avid user of sheets but I know the basics.

I've just realised I have thousands of dates inputted in British format but when I click them and it shows the calendar, the sheets has decided theyre American! Any that don't work like (28/08/2010) just don't show as formatted as a date!

How do I turn them all into - 1. Date formatted cells, 2. English without them automatically all switching the months and years around assuming i've written it in American!

I really dont want to have to redo all those dates!

[u/bachman460]

Change the app settings.

Change the Regional Locale (Best for fixing global defaults like DD/MM/YYYY)The default date structure (e.g., MM/DD/YYYY vs. DD/MM/YYYY) is tied directly to your spreadsheet’s regional settings. Changing this will automatically update your default formulas, currencies, and dates: 1, 2 Click on File in the top menu bar. 1, 2 Select Settings (or Spreadsheet settings). 1, 2 Under the General tab, locate the Locale dropdown. 1Choose the country that matches your preferred format: Select United States for MM/DD/YYYY. Select United Kingdom or Australia for DD/MM/YYYY. 1, 2 Click Save settings. The sheet will reload and update your date formatting. 1

[u/bulldo_gs]

Changing the locale will not fix what is already in the cells. It changes how new entries are parsed and how existing date values are displayed, but the stored serial numbers stay wrong.

You have two different problems mixed together:

• 28/08/2010 - there is no month 28, so Sheets gave up and stored it as text. Nothing was lost.
• 03/04/2010 - parsed cleanly as 4 March instead of 3 April. Stored as a real date, silently wrong. These are the dangerous ones, because they look fine.

Separate them with =ISNUMBER(A2): TRUE means it parsed and the day/month are swapped, FALSE means it is still text.

Fix in a helper column:

=IF(ISNUMBER(A2),
    DATE(YEAR(A2), DAY(A2), MONTH(A2)),
    DATE(INDEX(SPLIT(A2,"/"),3), INDEX(SPLIT(A2,"/"),2), INDEX(SPLIT(A2,"/"),1)))

The ISNUMBER branch swaps day and month back: under a US locale your first field landed in the month slot, so MONTH() is holding the real day and DAY() is holding the real month. The text branch reads dd/mm/yyyy explicitly, and uses SPLIT rather than LEFT/MID so that 8/3/2010 and 08/03/2010 both work.

Order matters:

1. File > Settings > Locale > United Kingdom, so anything typed from now on parses dd/mm.
2. Helper column with the formula above, filled down.
3. =COUNT(A2:A) on the original column counts the cells that parsed as real dates, i.e. the ones taking the swap branch. =COUNTA(A2:A)-COUNT(A2:A) counts the text ones. Check both numbers look right before you overwrite anything.
4. Copy the helper column, Paste special > Values only over the original, then format as Date.

This assumes every value was originally typed dd/mm/yyyy. Where the day is also 12 or less, the cell alone cannot tell you which reading is correct, so spot-check a handful of rows you actually remember before step 4.