Am 24.03.2015 um 20:09 schrieb V Stuart Foote: > Andreas Säger wrote >> All this conversion from strings to numbers is unrelated to the date >> acceptance patterns that determines how Calc turns your keyboard input >> into day numbers. > > No, it is not just for keyboard entry! And, it is certainly applicable for > format conversion of non-local date formats entered as text strings. >
No, it isn't. > The OP says his data is already entered in the sheet as text strings: > If _all_ the values are text, VALUE or DATEVALUE together with the right locale will do the job. If the text dates are result of a wrong csv import or paste-special then you may have text and wrong dates and VALUE will not convert the wrong dates which is why I suggested a combined formula for text and wrong dates. Alternatively, you can convert wrong text dates by means of Data>"Text to Columns" and regex replacement (again with the right locale). > "Column A has *text* strings that are DD/MM/YYYY format. > I want to make them real dates..." > > So, needs an efficient way for those text strings to be converted to dates > (in interger value). > If everything is text, there are 3 efficient ways to fix it _plus_ doing the import again with appropriate import options. > When the column selection is cell formatted as Date, these existing text > strings will show with a single quote preceding on the formula bar. > No. 03/13/2015 shows the apostrophe in US context. With any other locale you won't see any apostrophe. Likewise you do not see any apostrophe in front of 13/03/2015 in US context because that string is not a number at all. > The existing text stings are forcibly recast, easily done with a Selction, > then an Edit -> Find-Replace using Regular expression of "^[0-9]" and > replaced with "&" for the Selection--which will cast text strings to > numbers-- which now within a date field, as controlled by "date acceptance > pattern", become valid dates. > Yes, I use to use .+ (any chars) as search pattern which effectively does the same as retyping the value without leading apostrophes. Your pattern fails to convert "Jan 13 2015" in US context or dates with leading weekday name such as "Fr 13/03/2015" > <http://nabble.documentfoundation.org/file/n4144356/Find_Replace_RegEx_convertStringsDates.png> > > > So, for the example of existing text strings, enter the matching "Date > acceptance pattern" of D/M/Y (and suppress any others if needed)--and then > recast the text strings 'DD/MM/YYY as dates. > > Yes, all this applies if all the dates are text because you did not check the "special numbers" options for this import and the conversion fails anyway if the locale does not match the actual style of the string dates. If you import dates like 13/03/2015 with US locale and "special numbers" option you get string dates together with wrong dates. If the strings have an apostrophe or not does not matter. -- Copy the following 2 lines: 13/03/2015 01/02/2015 -- Paste-special text with "special numbers" and English (USA) language. The first value will be text because 13/3/2015 is not a number in US context. The text shows an apostrophe in the formula bar if your global locale is not US English because a numeric text. There will be no apostrophe if your global locale is English(US) because in this context the text is not numeric anyway. The second value will be the 2nd of January although it is meant to be first of February just like 13/03 was meant to be 13th of March. Ignoring the data import options may lead to text data and/or wrong data. It is far easier to do the import right than fixing wrong data. Other example: 2,12 2,123 imported with English locale, gives a text for the first value and integer 2123 for the second line. Imported with German/Russian/French locale, both values are decimals. The decimals may appear as 2.12 2.123 if your global locale is English. The locale on the import dialog lets you import comma decimals although your global setting are English. > > > -- > View this message in context: > http://nabble.documentfoundation.org/converting-txt-to-dates-tp4143826p4144356.html > Sent from the Users mailing list archive at Nabble.com. > -- To unsubscribe e-mail to: [email protected] 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
