Ask Your Question

[Feature Request] Option to permanently deactivate automatic date formatting [closed]

asked 2016-08-25 19:33:48 +0100

cm5_1 gravatar image

LibreOffice Calc automatically converts gene names like DEC1, MARCH7, SEP1 into date format, i.e. 12/01/16

This type of behavior was recently documented to introduce tons of errors into the scientific literature.

"Gene name errors are widespread in the scientific literature"

From the paper: "Automatic conversion of gene symbols to dates and floating-point numbers is a problematic feature of Excel software. The description of this problem and workarounds were first highlighted over a decade ago [1]—nevertheless, we find that these errors continue to pervade supplementary files in the scientific literature. To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc. "

The existing corrections for this problem must be done on a cell-by-cell formatting basis, which has been historically insufficient, as is evident from the linked paper.

I am requesting a an option to disable automatic date conversion. For Science!

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-09-10 23:02:49.894059

1 Answer

Sort by » oldest newest most voted

answered 2016-08-25 20:16:08 +0100

mark_t gravatar image

I just tested DEC1, MARCH7 and SEP1 in LibreOffice Calc and did not see this problem.

Do you have examples that do show the problem in Calc?

edit flag offensive delete link more


In fact, when you apply Format Cells, select Date and select a format, the three items stay as they are.

floris v gravatar imagefloris v ( 2016-08-25 20:28:17 +0100 )edit

Oh, excellent! Version 4.2 does the automatic conversion, i.e. it is impossible to type DEC1 in a cell and have it not immediately convert. 5.2 does not do this.

cm5_1 gravatar imagecm5_1 ( 2016-08-25 22:03:49 +0100 )edit

You still need to be careful, if you save in xls format from LibreOffice, then open in Excel it will still look correct, but if you then open the cell to edit, active cursor in the cell, and press enter without changing the content then Excel will incorrectly format as a date. I was using Excel 2010 to test this. It might be safer to alway enter as 'SEPT2, the ' will ensure it is treated as text in both LibreOffice and Excel.

mark_t gravatar imagemark_t ( 2016-08-25 23:04:49 +0100 )edit

When I enter "1/3", it is interpreted as a date, but I expect it to be interpreted as "one third", as a number. I have set cell format to "number -> General", so I do not expect date formatting to apply here.

Golar Ramblar gravatar imageGolar Ramblar ( 2019-08-08 11:39:43 +0100 )edit

Question Tools

1 follower


Asked: 2016-08-25 19:33:48 +0100

Seen: 162 times

Last updated: Aug 25 '16