7

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?

7

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
  • 2
    It would be good to see the thousand separator as well. – Przemyslaw Remin Dec 23 '17 at 8:48

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.