Bug 44367 - EDITING: Wrong display when inputting/viewing dates before 1.1.1900
Summary: EDITING: Wrong display when inputting/viewing dates before 1.1.1900
Status: CLOSED DUPLICATE of bug 44286
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.4 release
Hardware: Other Linux (All)
: medium normal
Assignee: Eike Rathke
Whiteboard: BSA
Depends on:
Reported: 2012-01-01 08:07 UTC by bricolemaison
Modified: 2013-11-27 15:38 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:

A sample sheet with 3 lines containing 3 dates 2 of which are 1 day back after input (7.79 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-01-01 09:21 UTC, bricolemaison
screenshot just before confirming the entry of 01/01/1700 in cell A5 (28.58 KB, image/png)
2012-01-01 09:35 UTC, bricolemaison
Screenshot just after clicking the green check for cell A5 (30.98 KB, image/png)
2012-01-01 09:38 UTC, bricolemaison
Another (maybe) interesting screenshot showing the same numerical value formatted as date and number (28.87 KB, image/png)
2012-01-03 10:17 UTC, bricolemaison
A simple sheet with 4 dates (2 before 28/07/1790, 2 after) and text explaining behaviour on my system (7.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-01-04 13:06 UTC, bricolemaison
A screenshot of previous attachment (52.77 KB, image/png)
2012-01-04 13:12 UTC, bricolemaison

Note You need to log in before you can comment on or make changes to this bug.
Description bricolemaison 2012-01-01 08:07:05 UTC
Problem description: since I upgraded to version 3.4.4, my files with dates before 1.1.1900 show one day less than they showed in version 3.3.4. It also happens when editing a cell formatted as a date and inputting a date before 1.1.1900: when hit return, the displayed date is one day less than inputted.

Steps to reproduce:
1. Open a sheet
2. Format a cell as date
3. Input a date before 01.01.1900, e.g. 03.04.1755, 3rd April 1755
4. hit return, the date becomes 02.04.1755, 2nd April 1755 (European date order)

Current behavior:

Expected behavior: The date should be correctly displayed

Platform (if different from the browser): Fedora 16
Browser: Mozilla/5.0 (X11; Linux i686; rv:9.0.1) Gecko/20100101 Firefox/9.0.1
Comment 1 Rainer Bielefeld Retired 2012-01-01 08:54:28 UTC
DUP of of related to "Bug 44286 - [EDITING] Calc decreases one day of a date typed (Brazilian-portuguese locale and timezone)"?

May I ask you to read  hints on <http://wiki.documentfoundation.org/BugReport>?
Then please:
- Write a meaningful Summary describing exactly what the problem is
- Attach a sample document (not only screenshot)
- Attach screenshots with comments if you believe that that might explain the 
  problem better than a text comment. Best way is to insert your screenshots
  into a DRAW document and to add comments that explain what you want to show
- Contribute a step by step instruction containing every key press and every 
  mouse click how to reproduce your problem (and if possible how to created a 
  sample document from the scratch)
- add information 
  -- concerning your settings 'Tools -> Options -> Calc -> Calculate'
  -- concerning your PC 
  -- concerning your OS (Version, Distribution, Language)
  -- concerning your LibO localization (UI language, Area)
  –- Libo settings that might be related to your problems 
  -- how you launch LibO and how you opened the sample document
  –- If you can contribute an OOo Issue that might be useful
  -- everything else crossing your mind after you read linked texts
Comment 2 bricolemaison 2012-01-01 09:21:34 UTC
Created attachment 55019 [details]
A sample sheet with 3 lines containing 3 dates 2 of which are 1 day back after input
Comment 3 bricolemaison 2012-01-01 09:34:29 UTC
Meaningful summary: when I enter a date before 28th July 1790 in a cell, the date goes back one day after confirmation of the input (enter or clicking the green check)

Sample document attached
Screenshots attached (one just before clicking the green chech and one after)
Step by step: in KDE, start Libreoffice calc. Input "01/01/1700" and return. Cell A1 then contains "31/12/99"

Settings: Tools Options Calculate: Date field 30.12.1899 selected. Every option selected, except "Itérations" and "Exactitude comme affiché" (French, these are check boxes #1 and #3)
PC processor       : 0 & 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Pentium(R) Dual  CPU  E2140  @ 1.60GHz
stepping        : 13
cpu MHz         : 1200.000
cache size      : 1024 KB

OS: 3.1.6-1.fc16.i686 #1 SMP Wed Dec 21 23:18:01 UTC 2011 i686 i686 i386 GNU/Linux

LibO localised in French, default language for documents French
Comment 4 bricolemaison 2012-01-01 09:35:47 UTC
Created attachment 55020 [details]
screenshot just before confirming the entry of 01/01/1700 in cell A5
Comment 5 bricolemaison 2012-01-01 09:38:36 UTC
Created attachment 55021 [details]
Screenshot just after clicking the green check for cell A5

In addition to the cell being inputted, 3 lines show the result with 3 different dates. I identified 28th July 1790 by dichotomy.
Comment 6 bricolemaison 2012-01-01 09:49:46 UTC
Sorry I did not say that this happens in the blank sheet displayed by Calc when freshly started. I do not need to open a specific document for it to happen.

Sorry also for the confusion, that was my first bug report using bug submission assistant.

Best wishes for 2012!
Comment 7 Rainer Bielefeld Retired 2012-01-01 10:45:50 UTC
With all my versions in sample I see
01.01.90	Entered as 01/01/1790
01.01.50	Entered as 01/01/1750
28.07.90	Entered as 28/07/1790

You would be astonished if I would tell you how many ways I know how to "in KDE, start Libreoffice calc." I have good reasons to ask you to mention every  mouse click ... .
Please contribute a sample containing dates before AND AFTER the critical 1.1.1900 - Currently I am a little clueless
Comment 8 bricolemaison 2012-01-01 11:47:57 UTC
As it appears, what you see is not what I see :-) (see screenshots). Again the version with the problem is 3.4.4

In a sense, the date seems to be correct in the file (I did reopen it on another PC with version 3.3.4 and dates are correct).

Concerning 1.1.1900, I was wrong in my first post. The date where things start to be happening is 28th July 1790. I was first thinking about 1.1.1900 because of the Excel limitation. I should not have presumed too quickly. I had to apply dichotomy to find the exact date, trying first 1.1.1700, then 1.1.1800, then 1.1.1750, etc.

Related to how I start Calc in KDE, I click on the K symbol in the task bar, then I choose Calc from the applications->office menu. Sorry if this looks unprecise, but I do not know how to be more specific. It also happens if I start Calc with a shortcut on the desktop.

Do you want me to try different settings about localisation?
Comment 9 GerardF 2012-01-01 13:34:41 UTC
Can *not* reproduce with 3.4.4 running on Windows Vista.
I also use French localisation & language.

May be related to KDE or profile corruption.
Can you try with a new profile ?
Comment 10 bricolemaison 2012-01-02 10:23:41 UTC
I tried to create a new user, logged in as this new user, started Calc with K symbol -> applications > office > calc.

Within the blank sheet proposed by Calc when starting, i input "1/1/1700" in cell A1 and after hitting enter, the value displayed is "12/31/99"

Localisation is standard English-US for interface and documents.

So neither my profile nor the localisation in French seems to be the origin of the problem.
Comment 11 Rainer Bielefeld Retired 2012-01-02 10:34:35 UTC
That's something really strange.

"Bug 44286 - [EDITING] Calc decreases one day of a date typed (Brazilian-portuguese locale and timezone)" also seems to be Linux Only

LinuxOnly for now.

Can you confirm my Linux suspect?
Comment 12 bricolemaison 2012-01-03 10:17:01 UTC
Created attachment 55092 [details]
Another (maybe) interesting screenshot showing the same numerical value formatted as date and number

I include a screenshot where I have inputted numerical values -39967 in cells A1 and B1 and -39966 in cells A2 and B2.

Then I formatted cells A1 and A2 as date (format 31/12/1999 in choice list for date format) and you can see that the two numerical values which differ by 1 are rendered as 2 dates differing by 2 (days).

That is fully consistent with the fact that the contents of the file are correct and, in my case, are displayed correctly on another PC with version 3.3.4

I hope this will help finding the problem
Comment 13 Rainer Bielefeld Retired 2012-01-04 11:04:37 UTC
May be this one is related to "Bug 44453 - FILESAVE FILEOPEN .xls: EXCEL Leap year bug has to be mentioned"? Can you please do further tests?
Comment 14 bricolemaison 2012-01-04 11:42:58 UTC
Looking at bug 44453, I have created with libre calc a sheet containing:

Cell A1: "28/2/1900"   ===>  correctly displayed as "28/02/00"
Cell A2: "=A1+1" =====> Displays "01/03/00", which is correct, 1900 was not a leap year

Then I saved the file as Excel

I opened the excel sheet with excel 2003 and I got the following displayed:

Cell A1: "29/02/00"
Cell A2: "01/03/00"

I guess this is an example for bug 44453 but probably does not help for this bug. I am sorry that I put a wrong title to this bug. There is so far no link with year 1900, the problem occurs around date 28th July 1790.

Do you want me to do another specific test? please tell me as I am running out of ideas.
Comment 15 tester8 2012-01-04 11:56:14 UTC
NOT reproduced with

LOdev 3.5.0beta2 
Ubuntu 10.04.3 x86
Linux 2.6.32-37-generic Russian UI

Can you please try with 3.5?
Comment 16 Rainer Bielefeld Retired 2012-01-04 12:09:49 UTC
1700 is one of those Gregorian Calendar exception without leap years - what ever that might mean.

> I am running out of ideas.

me too. This really is a hard nut.
The very strange thing is that your OOo actively modifies the input. 
I currently do not understand where exactly the ranges with that strange behavior are. Can you please contribute a short table similar to mine in Bug 44453 with 1 correct date and the first wrong one. Such table listings much more instructive than floating text with "start to be happening ..." - How can I know whether you look from the past to the future or vice versa? ;-)

May be such a listing can bring up new Ideas (but to be honest, currently I am not optimistic)
Comment 17 bricolemaison 2012-01-04 13:06:41 UTC
Created attachment 55134 [details]
A simple sheet with 4 dates (2 before 28/07/1790, 2 after) and text explaining behaviour on my system

As asked, another very simple sheet with cells A2 to A5 being inputted with 4 consecutive dates from 26th July 1790 to 29th July 1790, inclusive. The text in column E indicates what I see on my system.

I also attach a screenshot of how it is displayed on my system.
Comment 18 bricolemaison 2012-01-04 13:12:41 UTC
Created attachment 55135 [details]
A screenshot of previous attachment

As said, the screenshot of the sheet with the 4 consecutive dates, 2 of which are incorrectly displayed and the 2 last ones correctly shown.
Comment 19 Eike Rathke 2012-01-13 13:38:54 UTC
Confirmed with LO 3.4.4 unxlngx6 and LO 3.5 with system ICU 4.6, does not occur in current master built against internal ICU 4.2, so this is _reverse_ to bug 44286 :-(
Comment 20 Eike Rathke 2013-03-26 12:41:40 UTC
Not having around the environment for 3.4.x anymore, but I strongly believe this should now fixed with the fix for bug 44286 and related.

*** This bug has been marked as a duplicate of bug 44286 ***