![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Column A has text strings that are DD/MM/YYYY format.
I want to make them real dates.
I tried these 2 datevalue formulas but I can't make it work.
14/03/2015 Err:502
=DATEVALUE(TEXT(A1,"##/##/####"))
=DATEVALUE(A2)
Highlight the cells
Right click
FORMAT CELLS
NUMBERS tab
In the FORMAT CODE block at the bottom, put
DD/MM/YYYY
If DATEVALUE(A2) is giving an error, maybe there is something else in the
cell, or you are using English-US as language then it is an invalid date,
month=14?
You can also try with Menu/Data/Text to column after select the data, click
on column head and select in column type the day type.
Miguel Ángel.
No, this won't work. You've got to change the global locale option in
order to change the evaluation context for existing data.
Other method: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
DO 2015-03-20 12:00, Andreas Säger wrote:
Column A has text strings that are DD/MM/YYYY format.
I want to make them real dates.
I tried these 2 datevalue formulas but I can't make it work.14/03/2015 Err:502
=DATEVALUE(TEXT(A1,"##/##/####"))
=DATEVALUE(A2)Highlight the cells
Right click
FORMAT CELLS
NUMBERS tab
In the FORMAT CODE block at the bottom, put
DD/MM/YYYYNo, this won't work. You've got to change the global locale option in
order to change the evaluation context for existing data.Other method: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
Do you have the used date pattern defined? Look at (menu) - Tools - Language Settings - Languages at "Date acceptance patterns".
No. Do you?
Please leave original context in replies. Stripping original content makes it
more difficult to track conversations without going back through the entire
message thread.
Thanks, Tom 
Off topic...
Thomas Taylor wrote
Please leave original context in replies. Stripping original content
makes it
more difficult to track conversations without going back through the
entire
message thread.
Nope--not at all necessary, and in many cases an indiscriminate lack of
trimming makes the post much harder to read.
And, in those cases when context can't be followed these threads are always
posted to LibreOffice's Nabble based archive.
http://nabble.documentfoundation.org/Users-f1639498.html
You've the option to view thread listed by date-tme, or threaded.
Back on topic...
Regards Andras and Kaj's exchange--- yes adjusting the "Date acceptance
patterns" (Tools -> Options -> Languages:) to include DD/MM/YYYY --if not
already present for the local, will without any formula manipulations allow
the existing column of text strings to be correctly parsed as date cell
formatting.
Andras is correct though, the FORMAT CODE on the Format Cells dialog will
not have the desired affect, it only controls the display of data correctly
cast as date values.
Either formula based string conversion as Andras suggests, or adjustments to
the Date acceptance patterns that Kaj mentions will work.
OK, I installed the latest LibreOffice and tested
=VALUE("20-03-1999") => Err:502 (invalid argument)
Then I added date pattern D-M-Y which did not change anything.
The one and only relevant setting for the conversion of already existing
text is the global application locale above "date acceptance patterns"
in the language options.
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 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)))
Regarding DATEVALUE, it converts a string into a date value, but you have to take care of the date format if you do not use ISO. So you have to define the date pattern,
(menu) - Tools - Language Settings - Languages at "Date acceptance patterns".
Already Miguel Ángel pointed this out, that the fields in the date could be wrong, e.g. English (USA), and he was right.
I have not succeeded in modifying the pattern field directly, despite this should be possible according to help. But to change the "Local setting" three lines above worked fine. Now the default setting in this field is "English (USA)" with the attached pattern M/D/Y while we need D/M/Y. This is the standard for English (UK). With this done, all worked fine for me in my testing.
Regarding the setting (menu) - Format - Cells: This applies just for the "converted" cell, how the date shall be presented, and there you can chose anything you want, the default English (UK) (which you chose above), German (Germany) which gives the ISO standard, or anything you want. This formatting must not be applied to the cell where the original date is (e.g. cell A1). That cell should be formatted as text.
Kaj
Am 2015-03-24 02:16, Andreas Säger schrieb:
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).
There are no "date values" in spreadsheets. 0 formatted as date gives
1899-12-30, 36526 formatted as date gives 2000-01-01. Both values, the
"date value" and the integer are the exact same value displayed in
different number formats like you can display them in different fonts,
colors or sizes.
If the value is an integer day number without time, VALUE and DATEVALUE
return the exact same day number.
In English notation with point as decimal separator and comma as list
separator:
=VALUE("2000-1-1 12:00") => 36526.5 (full day number with time)
=DATEVALUE("2000-1-1 12:00") <=> INT(VALUE("2000-1-1 12:00")) => 36526
(integer day number only cutting of the time fraction of the day)
TIMEVALUE("3.14159") <=> MOD(VALUE("3.14159"),1) => 0.5 (the fraction of
the day cutting off the integer day number).
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.
Short and correct answer again:
1) Avoid text strings alltogether. Enter valid numbers into numeric
cells (no text formatting) and import/paste-special with the right
locale and "special numbers" option set.
The right locale for your 14/03/2015 is anything English but not US
English. The right locale for 03/14/2015 would be English(USA).
2) If things went wrong and you can't redo the import and you have
imported text values _mixed_with_wrong_dates_ (inverted day and month),
then you should switch to the right locale (British for 14/03/2015) and
apply this formula:
=IF(ISNUMBER(A1);DATE(YEAR(A1);DAY(A1);MONTH(A1));VALUE(A1))
which either inverts back the month and day portion of any wrong date or
converts any text date to the correct numeric cell value.
Then turn the formula results into constant numbers (copy &
paste-special values) switch back to your preferred locale and format to
your liking. Neither the locale nor the formatting will change any
correct value but the correct locale is required for the correct British
context of the string-to-number conversion of 14/03/2015. German,
Russian, French do work as well with D/M/Y dates but not US Enghlish.
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.
The OP says his data is already entered in the sheet as text strings:
"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).
When the column selection is cell formatted as Date, these existing text
strings will show with a single quote preceding on the formula bar.
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.
<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.
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.
Am 2015-03-24 18:22, Andreas Säger schrieb:
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.
There are no "date values" in spreadsheets. 0 formatted as date gives
1899-12-30, 36526 formatted as date gives 2000-01-01. Both values, the
"date value" and the integer are the exact same value displayed in
different number formats like you can display them in different fonts,
colors or sizes.
Well, you call them date numbers, and that is ok for me. But as they are not just any, but defined as the number of days passed after 1899-12-30, I cannot see anything wrong in calling them date value. And moreover they are not simple integers, since the time of day is included as a decimal part, just the way you describe in the next paragraph. Did you think I do not know that?
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.
Hi, I got a simple solution to this. My date format in excel sheet was like
12-04-2015.
Excel was not recognizing it as date or Formatting cell to Date was helping
me.
So I selected and Copied entire column of date to New Blank Word document.
Then I Again copied the date column from word document and came back to my
original excel sheet.
Then right click and press Pest Special -->Paste as TEXT.
Done. Now Excel will recognize it as date and will allow you to format cells
any date format as you wish.
Hi
I think you meant "Writer" rather than "Word"! Also "Calc" rather than
"Excel". However those sorts of tricks often work in all sorts of
different Office suites. I would probably have used a text-editor rather
than W* to avoid any weird formatting creeping in but you did the same by
paste-special - "as unformattted text"
Nicely done and a good answer imo
Regards from
Tom
What ever the wordings are, conveying idea is important. I am not "The
Expert" but just want to solve the issue using more simpler method rather
than formulating some creepy scripts.
Hi, I got a simple solution to this. My date format in excel sheet was like
12-04-2015.Excel was not recognizing it as date or Formatting cell to Date was helping
me.So I selected and Copied entire column of date to New Blank Word document.
Then I Again copied the date column from word document and came back to my
original excel sheet.Then right click and press Pest Special -->Paste as TEXT.
Done. Now Excel will recognize it as date and will allow you to format cells
any date format as you wish.
This __happens__ to work for you. What does "12-04-2015" actually mean?
On my system this will be interpreted as 12th of April. People with US
locale will get a date value referring to the 4th of December.
Then right click and press Pest Special -->Paste as TEXT.
When you paste-special unformatted text into LibreOffie Calc you will
get the text import dialog. There you need to choose the right locale
(any non-USA in this particular case) and you need to check the "special
numbers" option. Otherwise you will get text values again.
Your MS Office may be "easier" BUT if the origin of the text data
differs from the office locale it may import wrong data again and again.