29

I would like to have the "dd/MM/yyyy" (e.g. 09/03/2017) as default date format for my Google sheets. I know how to do this for one sheet:

Format > Number > More formats > More date and time formats ...

Or I could change the locale to Spanish for example (which has this date format) in:

File > Spreadsheet settings > Locale

But I would also like to have the dot (.) as decimal notation, and therefore I have the locale set to US. Therefore my question:

How can I set the decimal dot and the date format "dd/MM/yyyy" by default for the Google Spreadsheet?

1

2 Answers 2

22

Set the locale to one of (Australia, India, Ireland, Israel, or United Kingdom) to get date format 20/05/2017 and number format 1.25.

For the sake of completeness, I list the output of the formulas =today(), =5/4, and =99999/4 (cell formatted as "Number") for all currently available locales in Google Sheets.

Dot separates fractional part

Australia, India, Ireland, Israel, United Kingdom 
20/05/2017  1.25   24,999.75

Canada (English), Mongolia
2017-05-20  1.25   24,999.75

China
2017-5-20   1.25   24,999.75

Hong Kong
2017年5月20日 1.25  24,999.75

Japan
2017/05/20  1.25   24,999.75

Mexico, Thailand
20/5/2017   1.25   24,999.75

Philippines, United States
5/20/2017   1.25   24,999.75

South Korea
2017. 5. 20 1.25   24,999.75

Switzerland
20.05.2017  1.25   24'999.75

Taiwan
2017/5/20   1.25   24,999.75    

Comma separates fractional part

Argentina, Brazil
20/5/2017   1,25   24.999,75

Armenia, Azerbajian 
20.05.17    1,25   24.999,75

Belarus, Georgia, Kazakhstan
20.05.17    1,25   24 999,75  

Bolivia, Chile, Colombia, Ecuador, Paraguay, Uruguay, Venezuela
20/5/2017   1,25   24.999,75

Bulgaria, Norway, Russia, Ukraine 
20.05.2017  1,25   24 999,75

Germany, Romania, Turkey
20.05.2017  1,25   24.999,75

Canada (French), Lithuania, Poland, Sweden
2017-05-20  1,25   24 999,75

Croatia, Serbia
20.5.2017.  1,25   24.999,75

Czechia, Finland, Slovakia
20.5.2017   1,25   24 999,75

Denmark, Greece, Indonesia, Italy, Spain, Vietnam
20/05/2017  1,25   24.999,75

Egypt
2017/05/20  1٫25    24٬999٫75

France
20/05/2017  1,25   24 999,75

Hungary
2017.05.20. 1,25   24 999,75

Latvia
2017.20.5   1,25   24 999,75

Netherlands
20-5-2017   1,25   24.999,75

Portugal
2017/05/20  1,25   24 999,75

Slovenia
20. 5. 2017 1,25   24.999,75
3
  • 3
    It would be good to see the thousand separator as well. Dec 23, 2017 at 8:48
  • 1
    Looks like "Canada (English)" has ISO 8601-style dates and "." for decimal... nice. My new favorite locale. :)
    – lindes
    Aug 2, 2019 at 18:37
  • 2
    I do not know why this was set as the correct answer and has been upvoted so many times. It does NOT ANSWER the question of: How can I set the decimal dot and the date format "dd/MM/yyyy" by default for the Google Spreadsheet? The key word here is DEFAULT. Mar 6, 2021 at 4:38
4

To Default your locale:

  • Go to Google Drive

  • Select (Click on) your Profile Pic

  • Select "Manage your Google Account"

  • Select Data & Personalisation

  • Near the bottom is Language: enter image description here

  • Select your Language and then Locale, not every language is available for every Locale which is annoying, and vice versa.

Also some locale are present but they do not affect Google sheets. For example English - New Zealand is selectable in Google Account Settings but this sets Google sheets to English - UK. Very Annoying/confusing. Google should have created a common setting layout where the defaulting done in GSuite is then consistent in GSheets and GDocs etc. Anyway...

For me in NZ I choose Australia enter image description here

This is the result in a Google Sheet: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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