We've wandered away from the failure to convert 2000-02-29 and 2400-02-29 correctly on reading of those dates in saved Calc cells to a possible interaction with the Excel leap-year calendar bug. The Smoke test will also demonstrate whether or not that bug is evident in the Calc cases. It is not.
But the but should be revealed when transferring spreadsheets between Calc and Excel. - Dennis LOOKING AROUND FURTHER [It would be good to build dataflow diagrams that explain these passing of documents through different consumers and producers. What follow below is not so meticulous. It is a trial. In order to work through this more systematically, there need to be more carefully staged tests and alternative smoke tests.] In converting to Excel 2003 XML format, the result should be opened in Excel to see how much is preserved or not. The discrepancy for dates before 1900-03-01 should also appear. If the serial days column data is lost, that is a bug in the export (or the re-import). SAVING TO XLS (EXCEL 27/2000/ME) I saved the Smoke Test document to Excel 97/2000/ME .xls format. It round trips back into AOO just fine. All formatting is preserved. If I open the saved .xls in Excel, it will show the dates from serial days 0 to 2 as 1900-01-00 (!), 1900-01-01, and 1900-01-02. (It is not possible to enter 1900-01-00 as a date. Excel displays this because the 0 serial-day value was delivered in the cell exported by Calc. It is not possible to enter that date into the cell, though one can enter 0 into a numeric cell and then change its format to date, or obtain the 0 as a formula result, etc.) Excel also shows Serial Day 60 as 1900-02-29. For all later dates and serial-day numbers, Calc and Excel agree completely, and the Calc formatting is preserved in Excel also. I added that screen shot to show how the Excel "leap-year bug" shows up using the same smoke test document. LO 3.5.0 will open the .XLS file correctly as stored. (I see I have an error in the Smoke Test .ODP for the expected result in C16. It should be 2100-02-29, the value actually taken as text and not a date.) SAVING TO XML (EXCEL 2003 XML FORMAT) To save the Smoke Test as Excel 2003 XML format requires a JRE so it doesn't work on my bare Windows 8 64-bit and Vista SP1 32-bit test installs. To get quick results, I did the Excel 2003 XML format using the Oracle OOo-dev 3.4.0 that I have installed. On round trip of the Excel XML format, by the time it gets back into Calc, the date formatting is changed (1899-12-30 becomes 30-12-99 on my configuration) and the serial days formulas are some mangled versions of the OpenFormula formulas that were in the original .odp. These are all Calc defects somewhere in the export to import path. IMPORTANT: The roundtrip of this export DOES NOT demonstrate any 2000-02-29 or 2400-02-29 problem in OpenOffice or in Excel. Also, attempting to import this file into LO 3.5.0 fails with a General input/output error, perhaps because there is no JRE available. It opens in LO 3.3.2 with the same peculiar loss of formatting and column B formula mangling as in OOo-dev 3.4. When I correct the formula in B3 in OpenOffice Calc and do a fill down to populate the rest in OO.o Calc, the correct values are revealed. When I open the Excel XML version in Excel, there are a number of very strange things. I see the same date formatting as in the Smoke Test .ODP, but that is my default date format on the machine I ran Excel on, so it might be a coincidence. It appears that the export from Calc adjusts the date cell values with serial day value below 61 by subtracting 1, so the cell for 1889-12-30 comes into Excel with serial day number -1. The cells that OO.o did not accept leap day dates for came over as text (appropriately). On re-entering 1900-02-29 manually in cell A7, the serial day 60 shows up. In column B, the formulas were not accepted by Excel. Instead, Excel simply presented the values that were in the XML file as the last-calculated values and dropped the formulas. This is probably an OO.o export bug that is not converting OpenFormula to Excel correctly. It could be that the XML export had not been updated properly when OpenOffice.org converted to OpenFormula (assuming that it once worked). If I manually correct column be by making cell B3 hold "=A3", fill down, and also change the column format to numbers with commas, you can see how dates from 1900-03-01 onward are in agreement with OpenOffice but the earlier dates don't work, and the correction by -1 for earlier dates actually creates an Excel error for 1899-12-30. I made a screen capture of the cleaned-up Excel view also. -----Original Message----- From: Pedro Giffuni [mailto:[email protected]] Sent: Saturday, March 03, 2012 17:25 To: [email protected] Subject: Re: Nominate release blocker: 118999 - Leap year not correctly calculated On 03/03/12 17:34, Dennis E. Hamilton wrote: > Amen on understanding the scope of the bug!! > > As promised, I built a smoke-test document and ran it. The bug does not > appear at all in any Windows version of OpenOffice.org that I tested. In > particular, it does not appear in OpenOffice.org 3.3.0, in the Oracle OOo-dev > 3.4.0 developer release, nor in the Apache OpenOffice OOo-dev 3.4 Developer > Snapshot r1293550. > > For more grounding, I confirmed that the bug also is missing from LibreOffice > 3.3.2, the one I use for production, but it does appear in LibreOffice 3.5.0. > > So, whatever the origin of the defect, it apparently does not exist in the > Apache OpenOffice lineage from OpenOffice.org. > > On the other hand, it would be good to keep the smoketest document around, > just in case. > > The file and screen captures demonstrating the presence and absence of smoke > are all attacked to the AOO Bugzilla report #118999. > The code is only used for conversions and apparently recent versions of LO use it more aggressively but I can't find huge differences with what we do (not that I looked too hard). In any case I did a conversion of Dennis' file to Excel XML and while the result is ugly (the "Serial days" information is lost and 1899 is formatted "99"), the dates are still consistent. I am doing more tests converting stuff but for now I changed the status to "irreproducible". As originally planned I wont commit my patch for 3.4 but it still looks like a latent bug waiting to strike so I will test Dennis' file with my patch for inclusion after the release. Thank you, Dennis! cheers, Pedro.
