Am 25.03.2015 um 01:28 schrieb Kaj:
Am 2015-03-24 18:22, Andreas Säger schrieb:Am 24.03.2015 um 14:22 schrieb Kaj:Hi ! Now you happened to use the wrong conversion function, VALUE instead of DATEVALUE, so of course it did not work. But even with the correct function there seems to be some issues. According to the help text for the date acceptance pattern: besides local ways to write date, also the ISO standard is supported. This standard says that dates are written, like all numbers in the decimal system, with most significant values to the left and least significant figures to the right. Hence dates are written "YYYY-MM-DD". I tested this in my computer and it worked very well. Conversely I had no success with the format DD/MM/YYYY despite I had introduced this as a pattern. I also tested some string manipulation to convert the string like this =DATEVALUE(CONCATENATE(RIGHT(A1,4),"-",MID(A1,4,2),"-",LEFT(A,2)))and so does =VALUE(CONCATENATE(RIGHT(A1,4),"-",MID(A1,4,2),"-",LEFT(A,2)))and that worked fine too. However this is much the same as using the DATE function, proposed earlier by you Andreas: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) BUT! wrong! The DATE function converts numbers into a date value, so you have to convert the strings into numbers. Thus: =DATE(VALUE(RIGHT(A4,4)),VALUE(MID(A4,4,2)),VALUE(LEFT(A4,2)))Wrong. The DATE function calculates one integer day number from 3 numbers year, month and day. Without VALUE(), =DATE(RIGHT(A4,4),MID(A4,4,2),LEFT(A4,2)) works as well because Calc implicitly converts integer numerals (strings consisting of digits only).Not wrong, but possibly not fully exhaustive. Did I mention how many numbers that were input for the conversion? If my eyes are still working as expected I read the word "numbers" (plural) when describing the main procedure. Well, in one aspect I have to admit I was not fully informed. I was not aware of the implicit conversion of strings containing numbers into numbers. Good to know. However, despite this, I prefer to define this conversion explicitly to have full control and not being surprised at some possible change in the future.
The topic is about string to number conversion. After the conversion you have different cell values. A conversion between number and date does not take place. Formatting does not convert anything, The formatted values remain the same and all calculations yield the same results. Apart from errors, Calc has only 2 data types number and text. Excel has booleans as a separate data type. In Excel =TRUE=1 and FALSE=0 both return FALSE. In Calc the same comparison returns TRUE because 1 and TRUE are the exact same values of the same type. The original posting indicates that text has been imported due to a wrong locale. If the text values were the result of the missing "special numbers" option or the result of quoting or if they were explicitly marked as text, DATEVALUE or VALUE would do the conversion from text to number. The only locale unable to convert "14/03/2015" is the US locale and some Latin American locales with the same MDY date pattern. Thus we know that James tries to convert a "normal" date under a global US locale. He does not tell us how the bad dates got into his sheet but if there are more of these dates with smaller day numbers (day numbers 1 to 12), then he certainly has wrong numbers among the text values, dates such as 12/03/2015 referring to the 3rd of December instead 12 of March. In this particular case, you should really try to import the same data again using a UK locale and "special numbers" as import options. If the original file or the clipboard content is no longer available, the only way to convert such a bad date column is the formula I provided. -- To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted