There is an inconsistency how different spreadsheet functions treat their locale-specific arguments. TEXT [1] takes cell's locale when processing its 'Format' argument, and thus '=TEXT(0.1;"0 000.000")' will give different results depending if cell's number format is set to en-US or ru-RU: "0 000.100" vs "0 000. 000". DATEVALUE [2], OTOH, does not take cell's number format locale into account when parsing its argument, so that '=DATEVALUE("03 Jun 2019")' will give the same result irrespective of the cell's settings - it would be a date when program's locale is en-US; and it will be Err:502 when program is set to ru-RU locale. These should be made consistent, depending on the cell's locale to allow flexibility. [1] https://help.libreoffice.org/7.2/en-US/text/scalc/01/04060110.html?DbPAR=CALC#bm_id3147132 [2] https://help.libreoffice.org/latest/en-US/text/scalc/01/func_datevalue.html?DbPAR=CALC
Thank you for reporting the bug. Unfortunately without clear steps to reproduce it, we cannot track down the origin of the problem. Please provide a clearer set of step-by-step instructions on how to reproduce the problem. And cuold you specify the version of LO and Hardweare, please. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested information is provided.
Mike is a developer and all information needed by Calc devs is provided. (says a Calc dev ..).
For completeness I repeat some fragments here I just posted on Ask.LO: Taking the formula cell's locale into account isn't such a good idea, though it might help a little in some cases, like maybe this DATEVALUE(). It was done for TEXT() to be able to have *some* hint, and it has two fallbacks (current locale and en-US) if the format string can't be interpreted in the cell's locale. That's quite unpredictable but works in *most* cases. Which doesn't make it less of a mess.
DATEVALUE() is dangerous basically. Making its working depend on a cells locale will probably complicate things. And: What if the function only is a subexpression, and the relevant result appears in a different cell... Surprises to be expected anyway. An opinion: The inconcistency concerning TEXT() isn't actually important. The relevant problem with DATEVALUE() is that it's result factually depends on parameters (settings) neither clear to the user nor explicated by the help nor reasonably disambiguated in any way. That's not only concerning the locale used for the conversion, but also - and mainly- concerning the default century. This default will shift with time, and results can change without warning, and unnoticed if two-digit-years are accepted. The only way to reasonably handle the date-format-troubles, is to abandon them. "And by opposing end them." One step on the way would require to not convert ambiguous date-strings at all. If users urgently need the functionality, the indispensably needed information must be added. Yes, this requires optional parameters: A first one for the used format, and - in case of acceptance for two-digit-year-dates - the first year of the default century as the second one.
(In reply to Wolfgang Jäger from comment #4) > And: What if the function only is a > subexpression, and the relevant result appears in a different cell... > Surprises to be expected anyway. This is irrelevant to this issue. > The inconcistency concerning TEXT() isn't actually important. This is not correct. The issue here is that there *is* a method *for spreadsheet author* to guarantee *predictable* results in their formulas using TEXT, if they use cell format locale carefully. They know exactly how the end result will look like, irrespective of the locale the program is configured for. (Or they may opt to use the defaults, so they really have the control.) To the contrary, when they use DATEVALUE, they are bound to the program-level settings, and there's no way to ensure predictable input string processing (e.g., when input format is known) independent of user settings. (Note that *displaying* the result of DATEVALUE is unrelated to the problem: here we *only* discuss ho can we control the *direct behavior* of mentioned functions [which in case of DATEVALUE is conversion from text to number], not some post-processing of the results.) No matter what one might think about the problematic status quo of date/time representations in spreadsheets (which exist of course), or in this world in general, the discussed inconsistency is a problem disallowing useful applications.
(In reply to Mike Kaganski from comment #5) > To the contrary, when they use DATEVALUE, they are bound to the > program-level settings, and there's no way to ensure predictable input > string processing (e.g., when input format is known) independent of user > settings. I disagree. ISO 8601 exists and DATEVALUE() is able to parse its delimited form (or any TEXT(...;"YYYY-MM-DD") produced string) in any locale.
(In reply to Eike Rathke from comment #6) :-) You imply that "input format is known" implies "input format is ISO 8601" (which is not necessarily the case ;-))