Apache OpenOffice (AOO) Bugzilla – Issue 126908
date input bag
Last modified: 2016-04-08 04:38:23 UTC
I type in the cell the date 14 July 1919. Press ENTER. I read on July 13, 1919. The bug is present in the range of 27 April 1900 to 14 July 1919.
I cannot reproduce this behavior with Win8 and AOO 4.1.2. Please note that you can influence the starting date for date calculations via "Tools - Options - OpenOffice Calc - Calculate - Date" (Select the start date for the internal conversion from days to numbers). For more details have a look in the help topic. When changing this setting I can see a change in the displayed date. But this is wanted and not a bug.
It does not make sense that one can enter a date and then a different date is shown for the cell. The input and output conversions should be symmetrical for dates, no matter what the origin date is. That is, the same origin day (e.g., 1899-12-30 or 1900-01-01) should apply in both directions, and any leap-year correction should also. If this is not changed while working on the sheet, there should be no input-output discrepancy. This smells like a bug. Also, having the defect appear in this particular range is very strange. If it is about the 1900 leap-year tweak, what happens with 29 February 1900 should be evident. Nicolay, 1. Are you working in .ods format or are you starting/saving-in a different format? 2. Under Tools > Options > OpenOffice Calc > Calculate which date origin is selected: 1899-12-30, 1900-01-01, or 1904-01-01? 3. What are the actual forms of the dates that you enter and that are then displayed. That is, do you use "14 July 1919" and see "13 July 1919" or are the actual entered and displayed values different than those. Be specific. 4. For one of the cells that shows the defect, when you select the cell and Format > Cells ... what is the Category and "Format code" for the cell?
Revert to unconfirmed
Maybe a solution in this thread: https://forum.openoffice.org/en/forum/viewtopic.php?t=72644
(In reply to oooforum (fr) from comment #4) > Maybe a solution in this thread: > https://forum.openoffice.org/en/forum/viewtopic.php?t=72644 Yes, if the change being reported here happens only after reloading a saved file. If it happens immediately on entry of a date, that is a different problem. Also, it may well be that the origin-date is not saved as part of the .ods, which would account for discrepancies from one user to another. That would be a different defect. More information from this reporter may help isolate this better.
Sorry, I responded to comments by e-mail because they receive e-mail, too. I repeat them here. In a comment #1: I found the bug on a version of OO 3 ... The first thing I did Open Office has updated to version 4.1.2. Bug left. Asking your setting does not change the behavior of the program and the range of bug. Changing only the previously entered date. Date format also does not change anything. Maybe in the Russian version has some features? Sending video with a demonstration of a bug. In a comment #2: 1. I worked in the .xls format. But now I tried to save a document in a format .ods. Nothing changed. 2. 1899-12-30. When switching to other options improper input range does not change. 3. I tape: "14.07.1919". I see: "13.07.19" 4. Cell Format code is DD.MM.YY If I change the format code, changes the display accordingly. Number 14 still turns into 13. My more information (single email): Maybe the following will help you find a bug. I fill column serial numbers 7133, 7134, 7135, 7136, 7137. I select the filled cells and change their format to the format of the date code DD.MM.YY. I'm getting 11.07.19, 12.07.19, 13.07.19, 15.07.19, 16.07.19. Note that July 14 is absent. In a comment #4: No. The forum thread discussed the effect of the interaction of two documents with different zero dates. And my problem - bug in the conversion procedure "number to date", that is used when displaying the date: the shift by one day in the "yesterday" for the numbers from 119 to 7135. At the same time built-in functions DAY, MONTH, YEAR do not contain this bug - I tested.
Unfortunately I could not to attach here a video with a demonstration of a bug, which I sent by e-mail. Its size is about 30 MB.
(In reply to Nicolay from comment #7) > Unfortunately I could not to attach here a video with a demonstration of a > bug, which I sent by e-mail. Its size is about 30 MB. That's not necessary. Your previous comment is extremely helpful.
(In reply to Nicolay from comment #6) > My more information (single email): > Maybe the following will help you find a bug. > I fill column serial numbers 7133, 7134, 7135, 7136, 7137. > I select the filled cells and change their format to the format of the date > code DD.MM.YY. > I'm getting 11.07.19, 12.07.19, 13.07.19, 15.07.19, 16.07.19. > Note that July 14 is absent. When I do this exercise, I get 13.07.23 to 17.07.23, with no skips. That is with origin date 01/01/1904. With origin date 01/01/1900 I get 14.07.19 to 18.07.19. With origin date 12/30/1899 (in my UI), I get 12.07.19 to 16.07.19. So, I see no skips. I see another problem. The difference between dates with 12/31/1899 and 01/01/1900 should only be by 1 day later for 01/01/1900. The Help information does not account for this discrepancy. (The information about 01/01/1904 is also not helpful.) With origin date 01/01/1900 Excel 2016 reports that the .ods cannot be opened because it uses an unsupported date system. With origin date 12/31/1899 Excel 2016 (with default settings) shows the same results as Calc. Excel also agrees with Calc when I save the .ods from Calc with origin date 01/01/1904. Conclusions: 1. Calc does save date-origin information in the .ods, and Excel honors it when the 1899 and 1904 origin dates are used for converting serial day numbers to calendar dates. 2. The discrepancy with the 1900 origin is problematic but that setting should not be used any longer in any case. 3. It appears that the 1899 dates on Nicolay's system are too low sometimes and then there are jumps up to correct dates. 4. I added 0 and 1 to the list of test serial-day numbers. This was reported as 30.12.1899 on Calc, not 31.12.1899. Both are reported as 00.01.1900 by Excel 2016. (I believe that Excel does not allow 0 as a date, so it provided the earliest calendar date it does support.) Calc shows 30.12.1899 and 31.12.1899 for the two. 5. Note that with values 59-61, Excel shows calendar days 28.02.1900, 29.02.1900 (the famous 1900 leap-year bug that goes back at least to Lotus 1-2-3), and 01.03.1900. Calc shows 27.02.1900, 28.02.1900, and 01.03.1900. This is by design. The designers of the ODF format chose to insist that the leap-year discrepancy not be perpetuated. This means that Excel and Calc will disagree for dates earlier than 01.03.1900 and that is by design and why the 12/31/1899 origin adjustment is actually to 12/30/1899 to still interoperate with Excel from 1 March 1900 onward. QUESTION: Nicolay, do you see the same results for 0,1, and 59-61? UNRESOLVED: Why there are jumps with some implementations is still unresolved and we have not been able to reproduce it. If there are jumps up, there should be places where a jump "down" is accomplished by the same date twice in a row earlier.
(In reply to orcmid from comment #9) > UNRESOLVED: Why there are jumps with some implementations is still > unresolved and we have not been able to reproduce it. If there are jumps > up, there should be places where a jump "down" is accomplished by the same > date twice in a row earlier. This smells like a rounding bug. It would happen when the serial-day value is broken down into years and day-within-year values. This may be a hardware difference and it may be a run-time library difference. My tests are on Microsoft Windows 10 Pro x64 and Intel i7-3770 CPU chips. I don't know whether may system has a later version of the Microsoft VC++ redistributable runtime than is installed with Apache OpenOffice otherwise.
(In reply to Nicolay from comment #0) > I type in the cell the date 14 July 1919. Press ENTER. I read on July 13, > 1919. The bug is present in the range of 27 April 1900 to 14 July 1919. It would be useful for these cases to determine whether the value stored in the cell is an exact integer value and only appears to be an exact integer when rendered as a General Number value. This is the other end of potential sources for rounding and rounding-failure difficulties.
In my program with origin date 30.12.1899: -1 is 29.12.1899, 0 is 30.12.1899, 1 is 31.12.1899, 2 is 01.01.1900; 59 is 27.02.1900, 60 is 28.02.1900, 61 is 01.03.1900 With origin date 01.01.1900 - all dates are shifted by 2 days. That is all right here. With origin date 01.01.1904: -1 is 30.12.1903, 0 is 31.12 03, 1 is 01.01.1904, 2 is 02.01.1904; 59 is 28.02.1904, 60 is 29.02.1904, 61 is 01.03.1904 That is, there the zero date is not 01.01.04, as indicated by the switch, but 31.12.1903. Regarding jump down. Again with origin date 30.12.1899: 116 is 25.04.1900, 117 is 26.04.1900, 118 is 26.04.1900, 119 is 27.04.1900
By the way, if the origin date 01.01.1900 bottom jump looks: 114 25.04.00 115 26.04.00 116 26.04.00 117 27.04.00
If the origin date 01.01.1900 bottom jump looks: -1346 25.04.00 -1345 26.04.00 -1344 26.04.00 -1343 27.04.00 The apparent shift of the start date marked me in a comment #12 due to the fact that the zero date falls in the range declared by me in comments #0 bug.
This - information about the version I use: AOO412m3(Build:9782) - Rev. 1709696 2015-10-21 09:53:29 (Mi, 21 Okt 2015)