[libreoffice-users] Re: converting txt to dates

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: 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