Change Google Sheets default date format with other locale

Question

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?

Answer 1

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

Answer 2

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:

• 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

This is the result in a Google Sheet: