How can I change Google Spreadsheets to have ISO 8601 dates as default?

Question

I would love to set my default date format to be ISO 8601 (relevant xkcd) for all of my Google Spreadsheets. I'm well aware that I can manually change the date format for a given spreadsheet by:

1. Selecting the whole sheet, click Format > Number > More formats > 2008-09-26

And it correctly sets the date formatting for that sheet. However, I have to do this for each sheet, and even more annoyingly every new spreadsheet that I create.

My question: How do I set this date format to be my default?

Answer 1

To have ISO date format 2017-05-20 and decimal dot (like 1.25), use either of the following locales: Canada (English), Mongolia.

To have ISO date format 2017-05-20 and decimal comma (like 1,25), use any of the following locales: Canada (French), Lithuania, Poland, Sweden.

Reference: Complete list of options

Answer 2

I believe that the only way to achieve this is to set a locale in which this date format is a default one. For example I have it as default because I have Polish locale.

Link to settings: https://drive.google.com/settings

Answer 3

The international standard date notation is now YYYY-MM-DD == iso8601 https://en.wikipedia.org/wiki/ISO_8601 largest to smallest e.g. year-month-day 2019-11-11

My (European, Irish) current(and for past 30 years) "normal" date notation is DD/MM/YYYY. https://en.wikipedia.org/wiki/Date_and_time_notation_in_Ireland

To make dates "normal" for me in DD/MM/YYYY format you have to check these three things:

1. in your Drive settings Language - Change language settings - e.g. English Ireland This will set it for all newly created docs. You only have to do this once.

2. in Spreadsheet settings (if spreadsheet was created different language), change Locale - e.g. Ireland

3. in Spreadsheet if some dates already there they might have custom format US style or no style so individually change those, e.g. select

   • Format - Number - Date
   • Format - Number - Date and Time
   • Format - Number - More Formats - More date and time formats - ...

You have to check 2. and 3. for any older Spreadsheet or any Spreadsheet shared with you that might have US locale set or have individual US format date cells.

So, to make sure you are using date format iso8601(2014 or later I guess) in all your sheets you have to check the same 3 things but using Language setting, Locale and date format which has iso8601 as a standard, e.g. Polish, Hungarian, Canada, Sweden, . . . see @user135384 's list of options Change Google Sheets default date format with other locale on other answer.

This list of options will probably change over time as(or if!) iso8601 is adopted or if Google add formats e.g. locale English with iso8601.

Answer 4

Open a sheet, then File > Spreadsheet settings and set locale to Hungarian and display language to English.

As noted in a comment below, this is not strictly ISO-8601 dates, as it uses dots rather than hyphens to delimit, but it is as close as I could find.