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.)
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).
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
reproduced in LibO 3.6.0 master on Fedora 64 bit
In version 18.104.22.168 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
Still [Reproducible] with Server Installation of "LibreOffice 22.214.171.124 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
Please set Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug
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.
(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.
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:
Still reproducible in Version 126.96.36.199 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3)
Win7 64 bit
Confirmed Fedora 18 LO188.8.131.52
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
For an easyhack we don't need some code points?
i wish to work on this bug, can someone help with the code pointers?
Thanks for interesting in this bug
Try to ask in chats mentioned on this page:
In order to limit the confusion between ProposedEasyHack and EasyHack and to make queries much easier we are changing ProposedEasyHack to NeedsDevEval.
Thank you and apologies for the noise
Migrating Whiteboard tags to Keywords: (needsDevEval)
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: 184.108.40.206, Build ID: f3153a8b245191196a4b6b9abd1d0da16eead600 on Windows7.
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?
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.
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.