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.

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.

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 '14 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 '14 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.