2015 Archives by date, by thread · List index

# [libreoffice-users] Re: converting txt to dates

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