8

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:

  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?

4

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

  • 2
    If that's the only way to do this, that's too bad! Unfortunately I don't speak Polish. – JBWhitmore May 7 '14 at 21:06
  • 1
    You could try Hungarian, then .... – LeeGee Jun 4 '17 at 8:14
  • But this also changes the default currency, which is then wrong! – Michael Jun 27 '18 at 1:57
  • Thanks, it changed the dates to ISO, but unfortunatelly changed numbers to format with a "," instead of "." as decimal point. – BoltKey Jun 7 at 15:44
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

0

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.

0

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.

New contributor
gaoithe is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.