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.
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:
####-##-##
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.
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.
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