2

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.

Improve this question

2 Answers 2

Reset to default
3

The following custom format code should work:

####-##-##

enter image description here

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.

Improve this answer
2
  • 1
    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.
    – SPRBRN
    Mar 2, 2014 at 16:12
  • 1
    @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!
    – tohuwawohu
    Mar 2, 2014 at 17:43
0

Great answer by tohuwawohu. I would suggest this as an alternative:

=DATE(QUOTIENT(A1;10000);QUOTIENT(MOD(A1;10000);100);MOD(A1;100))

You might want to format the cell after you have applied this formula.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .