Libreoffice: format field yyyymmdd to date

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.

SPRBRN

Posted 2014-03-02T11:47:28.043

Reputation: 5 185

Answers

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.

tohuwawohu

Posted 2014-03-02T11:47:28.043

Reputation: 8 627

1Thank 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 – 2014-03-02T16:12:51.143

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 – 2014-03-02T17:43:11.637

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.

Elmex80s

Posted 2014-03-02T11:47:28.043

Reputation: 101