Calc language, date and function separator settings

English
#1

I’m in Belgium but I want everything in english language. But at the same time:

  • DD/MM/YY for a date
  • english formula with , as a separator
  • euro (symbol) for currency

KDE is set for english. In the options/languages I have set everything to “english (USA)”. Then changed the currency to “euro dutch (belgium)”. Then I changed the “date acceptance pattern” to “D/M/Y;D/M”.

This does not work, if I enter a cell and put “=now()” I get a US date of M/D/Y.

If I change the locale setting to “dutch (belgium)” I do get the correct date format D/M/Y.

But now it changes the function separator to “;”. And I cannot change it back to “,”. If I change it in the options/LibreOffice Calc/formula back to “,” this does not work. It continues to give an error if I use “,” in the formulas as a separator.

Why are those options impossible to set independently from the locale?

#3

You can switch between the localised Formulas and the “original” English formulas in the settings:
Tools - Options - LibreOffice Calc - Formula - Use English function names

The sign “,” is the decimal separator in many languages. Therefore is is better to use the “;” sign as the separator in the formulas.

1 Like
#4

Does Belgium locale use comma as decimal separator? That would make comma-as-argument-separator impossible.

1 Like
#5

Tools>Options>LanguageSettings>Languages
Locale = English (Ireland)

Recommended date acceptance: D/;D/M/;D/M/Y (treats 1/2/ as date but not 1/2)

2 Likes
#6

Indeed, the belgium locale has “,” as a decimal separator. But I have set in KDE the decimal separator to be “.”, like it is on the numerical keypad.

I have now unchecked the option “decimal separator same as locale setting”. But even then it let me not select the “,” as a separator for functions.

Why is it not possible to set all these things separately, independently from the locale? I get external reports and they all are with “.” as decimal separator (in strings). So if I set the dutch locale, then I each time have to replace those befor conversion. Likewise dates but those are all in D/M/Y. If I set that I want to use english formulas, then it would be logical to have the “,” as separator. As long as there are no conflicts. But there are, only I cannot set the decimal separator independently of the locale…

Or could I do it in the expert configuration?

#7

Ok, I get it: “date recognition” is different from “date representation”. That is new to me but very useful.
That “locale = Ireland” looks to be the solution! It has the right date format, the euro symbol, “.” decimal separator, keeps the formulas in english and I get the “,” as formula separator. I can live with that :smile:

1 Like
#8

Because this program deals with documents.

#9

Problem is that the preconceived notion of how documents “should be” is wrong. We live in an international environment, the idea of a single locale that ties this all together is not usable anymore.
It is not that it matters as internally the representation of dates and numbers and formulas is tokenised and so transportable and presentable in any form wished by the user.

#10

Or you tell calc to import data in US-Format during import or at text-in-columns conversion… Even easier if done by macro or via base. There are several threads on csv-imports on this ask-site.

#11

LibreOffice allows you to edit human language and numerals in completely heterogenous environments. With styles and templates you can edit all kinds of documents in multiple languages presenting many types of numeric information under any chosen user interface language because it is does not obey to customized setups on OS level.

#12

[quote=“Wanderer, post:10, topic:80899, full:true”]Or you tell calc to import data in US-Format during import or at text-in-columns conversion… Even easier if done by macro or via base. There are several threads on csv-imports on this ask-site.
[/quote]
Having to import and set the format each time or do text-to-columns would take to much time. The way I work is to paste the whole report into a tab and then get the values I need out with formulas. But if you need to do “,” to “.” conversions (and others) as well, it becomes just unwieldy. Certainly when the lot approaches 500000 rows.
I don’t use macros because it is just to much work when the names of the files change each time. Also it isn’t transportable with colleagues using excel. (another issue)

#13

CSV is NOT a spreadsheet format. In fact it is not even any file format at all. It is an emergency solution to import database data when the source database is inaccessible. Working with plain text tables requires some level of expertise.

  1. If your import settings are always the same, just hit Enter when the dialog pops up.
  2. You can link a document sheets to csv files with pre-defined import settings.
  3. Dozends of macros have been written to import text with hard coded preferences.
  4. There are several ways to handle csv by means of the Base component or by some database engine of your choice. [Example] Loading CSV into preformatted spreadsheets

Fixing wrongly imported data by means of Text2Columns or Find&Replace should be avoided completely because there is a high risk of making things worse.

#14

I have to work with csv as otherwise I get files with merged cells/columns (over different ranges) and inserted logo’s making it even harder to get at the data.

1: no, they are for each file that has to be included different. Different sepatators, different formats for date and numbers and so on.
2: this might be a solution if it can cope with different filenames and directories each month the unified report has to be made.
3: hard coded is the issue I think, see 2
4: I’ll read it, thanks for the link.

I agree that text2columns and find and replace isn’t the solution. That is why I load each of the first reports into a sheet without any changes. And then get my data out of them using formulas. First a step getting rid of unused values, formating like eliminating spaces, converting text to values, trapping vlookups returning errors etc. Then I go to the last step working from my intermediate results.
Yes, it is ugly and it would be better if this was done with real programming. But that is what I have at the office and they do not want to supply anything else. Heck I even have to work on a portable Libreoffice because on a dutch excel it is just impossible to work, taking more time looking up and debugging the translations.

#2

Format date

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.