Google-sheets – How to change Google Spreadsheets to have iso 8601 dates as default

dategoogle sheets

I would love to set my default date-setting 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:

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?

Best Answer

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

Manual paste

Indeed, pasting plain text with Ctrl-V results in it having the default size of 10. It seems that pasting is understood to overwrite the formatting of the cell, even if it's plain text coming in.

The solution is to paste using Ctrl-Shift-V, which means paste values only. Then the font size is 12 pt, as set in the spreadsheet, regardless of where the text came.

Script modification

Generally, when you simply use .setValue(value), the existing formatting of the cell remains. For example, if the cell A1 was formatted as "1 Jun 2015" then executing

sheet.getRange("A1").setValue(new Date());

will respect that formatting.

The format is changed only if the script explicitly applies a different formatting with Utilities.formatDate(). I would say this is to be expected, and the fact that the old version behaved differently is more of a bug or poor design.

It's known that the new sheets have a somewhat different format functionality.

Migration can be a breaking change for some scripts: for example, one of my scripts stopped working because it tried to store more than 50000 characters in a single cell (which was possible with the old version, but not with the new one).

Google-sheets – How to edit dates in Google Sheets as ISO 8601

Partial answer

According to https://en.wikipedia.org/wiki/Date_format_by_country, the following locales use YMD date format:

Afghanistan, Albania, Bangladesh, Cambodia, Canada, China, People's Republic of, Denmark, Germany, Hong Kong, Hungary, Iran, Japan, Kenya, Korea, Latvia, Lithuania, Macau, Mongolia, Myanmar, Namibia, Nepal, Norway, Singapore, Slovenia, South Africa, Sri Lanka, Sweden, Taiwan,Republic of China, United Kingdom, United States of America

Notes

I didn't find a simple table that combines the date format and currency symbol but find the following that could help

Other interesting findings

Spefications / open source projects

Google participates in the below projects, so is very likely that Google Sheets use them

http://cldr.unicode.org/

The Unicode CLDR provides key building blocks for software to support the world's languages, with the largest and most extensive standard repository of locale data available. This data is used by a wide spectrum of companies for their software internationalization and localization, adapting software to the conventions of different languages for such common software tasks

http://site.icu-project.org/

ICU is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software applications. ICU is widely portable and gives applications the same results on all platforms and between C/C++ and Java software.

Tools

http://demo.icu-project.org/icu-bin/locexp

This demo illustrates the International Components for Unicode localization data. The data covers two hundred five different languages, further divided into four hundred eighty-eight regions and variants. For each language, data such as days of the week, months, and their abbreviations are defined.