Libreoffice: format field yyyymmdd to date

Question

How can I format a field value like 20140226 to 2014-02-26 in LibreOffice? If I use the format cell option I get strange values.

Solution

The following custom format code should work:

####-##-##

Please notice that this won't result in a "real" date value - it's the same integer value as before, only differently formatted. If you want to "translate" the number "20140226" into a date value, you should apply the following formula:

=DATEVALUE(TEXT(A1;"####-##-##"))

(assuming A1 holds a numeric value like "20140226"; it uses the TEXT() function to format the numeric value to a string thats looks like a date value, and the DATEVALUE() function to create a "real" date value based on the output of TEXT()).

The result will at first look strange (like 4-16-40 for the date 2014-01-01). Format the field like you normally do: Format > Cells and choose Date and the proper format.

Comments

[SPRBRN]

Thank you. I edited your answer as the comma in the formula didn't work. It needs a semi colon. Additionaly I gave an example of the confusing result you get.

[cyberha4]

@rxt: Argument separators depend on l10n, so it's hard to predict which version (comma or semicolon) suits better in a certain case. Some users may have to use commata, some will need to use semicola. Good idea to give an example for the "strange" result!