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
Best Answer
The following formula does the job (tested with U.S. and Russia as locales, where separators are different):
Explanation:
IFERROR
returns the first argument unless it throws an error, in which case it returns the second.IFERROR
is the result of converting the text, with any commas replaced by dots, into a number.IFERROR
is the result of converting the text, with any dots replaced by commas, into a number.I'm assuming that in every locale, one of two things is going to work.