Bug 36175 - Cells keep date autoformat incorrectly
Summary: Cells keep date autoformat incorrectly
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.6 all versions
Hardware: Other All
: high major
Assignee: Not Assigned
Depends on:
Reported: 2011-04-12 09:48 UTC by Quintin Hill
Modified: 2017-08-28 09:20 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Quintin Hill 2011-04-12 09:48:28 UTC
Enter 5/4 into a cell in the spreadsheet, Calc autoformats this to a date.  Now if you correct this to =5/4 as originally intended the cell is displayed as 31/12/99 instead of the expected 1.25. This is surprising and annoying for users. The date formatting that was applied automatically should not be retained.  (Also note that the date that appears here differs with that produced by Excel.)
Comment 1 Rainer Bielefeld Retired 2011-05-03 00:00:33 UTC
I think ist's reproducible with "LibreOffice 3.4Beta3  – WIN7  Home Premium  (64bit) German UI [DEV300m103 (Build:3)]" for a spreadsheet with English language. 

May be it's an easy hack to modify the code for an other behavior, bit I doubt that it will be easy to define what "expected behavior" might be. 

It seems that cell formatting remains as "date" after LibO has decided once that the inserted number is a date. That can be useful for some applications, for other ones it is unexpected. Additionally I see some inconsistence. For Example in a German spreadsheet, when I accidently type "1.11." the cell will show 1.11.2011. When I modify cell to intended "1,11", cell will NOT show "31.12.99", but "1,25", here the auto-date-formatting will not be kept.

Additionally it should be considered that also WRITER text formulas are affected, I see similar effects as reported here (but did not investigate systematically).
Comment 2 Björn Michaelsen 2011-12-23 12:03:40 UTC Comment hidden (obsolete)
Comment 3 sasha.libreoffice 2012-01-24 08:10:22 UTC
reproduced in LibO 3.6.0 master on Fedora 64 bit
Comment 4 Claude 2012-07-20 20:08:34 UTC
In version behavior is erratic.
On a new spreadsheet introducing 5/4 in a cell does not convert it to a date.  (USA English Language - Colombia locale)
Introducing 20/7 in a previously date formatted (format MM-DD) cell displays de string 20/7 and it is not recognized as a date (As it was in versiones 3.5)
Introducing the sequence 7-20 works fine but only if the cell was previously formatted.  If the same format is applied to the string already introduced, it is not taken as a formatting instruccion and the string 20/7 reamains displayed
Windows XP SP3
User Interface English - USA
Locale Seting  Spanish-Colombia
Comment 5 Rainer Bielefeld Retired 2012-07-21 05:46:43 UTC
Still [Reproducible] with Server Installation of  "LibreOffice rc  English UI/ German Locale [Build-ID:  815c576] on German WIN7 Home Premium (64bit), but has become less important because 3.6 handles date recognition more restrictive.

Steps how to reproduce with a.m. Version:
0. Open new spreadsheet 
1. Into A1 Type "=3-3-3"  <enter>
    As expected Result of calculation shown "-3"
2. Into A2 Type "3-3-3"  <enter>
    As expected Changed to date "1899-12-27"
3. Into A2 Type "=3-3-3"  <enter>
   Expected: Result of calculation shown "-3"
   Actual: date "1899-12-27"
Your problem is something completely different from the original report

@Spreadsheet Team
Please set Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug
Comment 6 Eike Rathke 2012-07-23 08:25:07 UTC
Retaining an existing cell format when entering a formula is on purpose.
However, what we could do here is a special treatment of date formats and clear them if the formula expression entered does not deliver a result of type date.
Comment 7 Rainer Bielefeld Retired 2012-07-23 08:32:54 UTC
(In reply to comment #6)
>  if the formula expression entered does not deliver a result of type date.

Sounds promising. The question is how much work we should invest and how much risk we can accept for such an IMHO not very important problem.
Comment 8 inpost 2013-05-08 06:32:08 UTC
A cell is formatted as 'date' dd/mm/yy

Value '01-04-13' is entered.

After hitting the enter button, the formula bar changes to:


The cell changes to:

Comment 9 Dries Feys 2013-05-08 12:40:50 UTC
Still reproducible in Version (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3)

Win7 64 bit
Comment 10 Tim Lloyd 2013-05-08 21:43:11 UTC
Confirmed Fedora 18 LO4.0.2.2

Enter 5/4 gives 05/04/13
Enter =5/4 in the same cell gives 31/12/99

Enter =5/4 in a separate cell gives 1.25 as expected
Comment 11 Ricardo Montania 2013-07-15 09:21:22 UTC
For an easyhack we don't need some code points?
Comment 12 Rachit Gupta 2014-02-10 13:01:23 UTC
i wish to work on this bug, can someone help with the code pointers?
Comment 13 sasha.libreoffice 2014-02-10 13:06:22 UTC
Thanks for interesting in this bug
Try to ask in chats mentioned on this page:  
Comment 14 Joel Madero 2014-02-27 22:55:17 UTC Comment hidden (obsolete)
Comment 15 Robinson Tryon (qubit) 2015-12-13 11:21:29 UTC Comment hidden (obsolete)
Comment 16 BARBARA POKORNOWSKI 2016-02-01 01:10:25 UTC
When a cell in Calc is formatted as "Date" "12/31/99" "M/D/YY" Default English, when one enters 2/1, the output is 2/1/16, which is correct. When one enters 3/4, the output is the fraction 3/4, instead of the date 3/4/16. This is with LibreOffice Version:, Build ID: f3153a8b245191196a4b6b9abd1d0da16eead600 on Windows7.
Comment 17 Shinnok 2017-08-25 12:55:20 UTC
Heiko, I briefly tested this myself and I can confirm the behavior is still here for target v6. Can you confirm that this is indeed a usability issue to be resolved?

If confirmed, can Markus & Heiko provide some pointers for hunting this one out?
Comment 18 Eike Rathke 2017-08-25 14:59:41 UTC
If one enters a formula, pre-existing formatting will not be discarded unless it was Boolean. The reason is that internally date+time is only a date serial number and any number can be formatted as date+time (value in days since null date, so 0.5 == 12 hours). In case of =4/5 the actual value 0.8 formatted as date+time is 1899-12-30 19:12:00

We maybe could check if the final result of a calculation of a newly entered formula expression could actually be a date and remove a date format if not, but that somehow sounds like overkill and again could be error prone and wrong in cases where some numeric calculation is done and the user actually wanted the result be formatted as date.

Fwiw, Excel behaves the same and keeps the date format.
Comment 19 Heiko Tietze 2017-08-28 09:20:21 UTC
If we clear the formatting on (any) input we would break down much more as benefiting for the datetime input. There are plenty of options to modify the cell format later, and as Eike said the situation is the same on Excel. So let's close this ticket as WF.