Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index

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

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"


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


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


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
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:
Sent from the Users mailing list archive at

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.