Description: I have a spreadsheet with lot of rows. As I need them to upload them as CSV on a web project I work on, I split them up into sets of 500 rows. Therefore I marked 500 rows, copied and pasted them into another spreadsheet. By chance we noticed that the date of one row changed from '27-Jul-93' to '27-Jul-89'. I copied and pasted it again. Same error. I just copied and pasted the cell. Same error. I have nearly 4000 rows and do not have the time to double-check every single cell. A copy and paste should not manipulate the data. Steps to Reproduce: 1. Open the .ods file 2. Copy the rows 3. Open a new spreadsheet 4. Paste the rows 5. Go back to the original file and copy just the cell G2 6. Paste the cell into the new document Actual Results: 4. Rows should be identical 6. Cell should be identical Expected Results: 4. Content in Column 'Date' changed from '27-Jul-93' to '27-Jul-89' 6. Content in Cell changed from '27-Jul-93' to '27-Jul-89' Reproducible: Always User Profile Reset: No Additional Info: Just copy the row / cell without manipulating the content.
Created attachment 164366 [details] File to reproduce the bug
The content changes from '27-Jul-93' to '26-Jul-89'
Repro Version: 7.1.0.0.alpha0+ (x64) Build ID: <buildversion> CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win Locale: nl-NL (nl_NL); UI: en-US Calc: CL and with 6.0.5 and with Versie: 4.4.7.2 Build ID: f3153a8b245191196a4b6b9abd1d0da16eead600 Locale: nl_NL
Also in LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4
@Eike Is there some explanation for this; I'm slightly confused
Yes, the attached source document has Nulldate 1904-01-01 while a new target document has Nulldate 1899-12-31, thus 4 years and 1 day difference. As all calendar dates are just numbers of days since nulldate, copying those numbers between documents with different nulldates goes awry. The only solution would be to detect different nulldates and in that case inspect each and every cell copied whether it is date afflicted (i.e. a date or date+time number format set) and if so do an adjustment (here +1462 days). My recommendation for the use case here would be to change the nulldate of the source document (Tools->Options->Calc->Calculate) to 1899-12-31 (which temporarily gives wrong dates) and then add 1462 to all date cells in the affected Date column. To do so enter 1462 in some other cell, copy that to clipboard, select the Date column range and then Paste-Special (Shift+Ctrl+V) with the Options Add being activated.
Ok, that's quite a weird behaviour. Why doesn't LibreOffice handle dates as almost every other software tool? Count the milliseconds since 1 Jan 1970 as 64-bit number. This is enough to go nearly 300 million years for and back. Thanks anyway for the quick answer!
(In reply to Eike Rathke from comment #6) What me surprised the most where is they 1904-01-01 nulldate coming from?
(In reply to komape from comment #7) > Why doesn't LibreOffice handle dates as almost every other software tool? > Count the milliseconds since 1 Jan 1970 as 64-bit number. Because no other spreadsheet software does it that way and interoperability demands to do it like it's done. (In reply to Telesto from comment #8) > (In reply to Eike Rathke from comment #6) > What me surprised the most where is they 1904-01-01 nulldate coming from? MS-Excel for Macintosh.
(In reply to Eike Rathke from comment #9) > Because no other spreadsheet software does it that way and interoperability > demands to do it like it's done. Really? When did this start? For me, it just seems that this can lead so easily to errors. (In reply to Eike Rathke from comment #9)hey 1904-01-01 nulldate coming from? > MS-Excel for Macintosh. This explains a lot. Got the original excel file from a person who uses a Mac. I converted the file to a .ods file. Thanks for your helping me and sharing the details of the cause!
(In reply to komape from comment #10) > When did this start? With Lotus 1-2-3, followed by Excel because they attacked that so did whatever it did.
(In reply to Eike Rathke from comment #6) > ... > The only solution would be to detect different nulldates and in that case > inspect each and every cell copied whether it is date afflicted (i.e. a date > or date+time number format set) and if so do an adjustment (here +1462 days). > ... I would doubt if this helps reliably. There is no value-type Date or DateTime in Calc, and the the related NumbrFormat types (2 through 7) are no safe surrogate, because any user can use different NumberFormat types suitable for the purposes. Unfortunately a fomula like ="2001-11-18" - 14878 is accepted by Calc, and neither Calc's automatisms nor the supposed user will format the result as date. The result will depend on the .NullDate setting, however. Without going into the Tokens of every formula cell there is no way to solve the prpoblem technically. And what about value-only-copies of such cells? Since there is no satisfying solution to the problem itself, there should be at least an enhancement helping to avoid serious errors which may occur if the mentioned behaviour lead to an unnoticed offset of 1462 days. When copying cell ranges from sheet to sheet and also when moving or copying complete sheets from one document to a different one, Calc should check the .NullDate settings of both documents and warn the user. See also https://ask.libreoffice.org/en/question/300357/.
(In reply to Wolfgang Jäger from comment #12) > I would doubt if this helps reliably. There is no value-type Date or > DateTime in Calc, and the the related NumbrFormat types (2 through 7) are no > safe surrogate, because any user can use different NumberFormat types > suitable for the purposes. And any number format using at least one of the date format codes is categorized as date format, so that is no exception to what I mentioned. > Unfortunately a fomula like ="2001-11-18" - 14878 > is accepted by Calc, That depends on the detailed calculation settings, whether on-the-fly conversion from text to numeric is allowed or not, and how. My recommendation is to set that to "Always generate #VALUE! error" (and use DATE() in this case), but it will break a gazillion existing Excel (and Calc) documents, but is the best setting to spot errors early. > and neither Calc's automatisms nor the supposed user > will format the result as date. The result will depend on the .NullDate > setting, however. The result *number* (serial date number) yes, if interpreted as date e.g. for display then no, the displayed date will be the same. Conversion from "2001-11-18" to the serial date number involves the current null date, from the resulting date serial the number 14878 is subtracted and the result is displayed adding it to the null date. > Without going into the Tokens of every formula cell there is no way to solve > the prpoblem technically. Which wouldn't help here either, because it's just a string token. > And what about value-only-copies of such cells? Yes, that will be different, and there's not much that could be done. > Since there is no satisfying solution to the problem itself, there should be > at least an enhancement helping to avoid serious errors which may occur if > the mentioned behaviour lead to an unnoticed offset of 1462 days. > > When copying cell ranges from sheet to sheet and also when moving or copying > complete sheets from one document to a different one, Calc should check the > .NullDate settings of both documents and warn the user. That would be a possibility.
Dear komape, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug