GNOME Bugzilla – Bug 490419
Date constants change values when moved between sheets with different date convensions
Last modified: 2008-04-10 18:54:00 UTC
In gnumeric-1.7.13, I have a date-formatted cell which when pasted into a new file has its value changed from 9/28/2011 to 9/27/2007. If I copy/paste it back to the initial file, then it comes back to the original value. Sample files attached...
Created attachment 97888 [details] original file with date
Created attachment 97889 [details] destination file with wrong date pasted into it
The source uses date convention 1904, like Excel does on the Mac. An importer, probably Excel, must have chosen it. Certainly confusing for the user. But since there is no fundamental distinction between dates and numbers in spreadsheets, a workaround may lead to even more confusion.
The source is in 1904-base, but that is not the real problem here, I think. We are treating this as a plain number. I think that is because the cell does not have a format, only the number in the cell does. If we format as a date, then things actually paste correctly. (Which, pick your poison, means that the value changes by 4*365, give or take one.)
I have fixed a close cousin of this problem: text targets (such as when pasting into Emacs) now take the value format into account.
I have now also added the date convention to the clipboard xml. That is certainly the right thing to do, but I am not sure how to proceed from here. We could refuse to paste into a workbook with different date convention, or at the very least warn when that happens. That should not be often. We could also detect cells with date formats in them and have the receiving gnumeric add an offset for those cells if the date formats are different. Ideas welcome.
I'm pasting date-formatted data from one file into another, and it still changes the date. A way to deal with that issue would be to, when pasting date-formatted data from clipboard, to ask the user to choose which convention gives the correct result. It seems pretty easy to detect an error when a 2007 date is pasted as 2011... Another option would be to make the user choose the date convention in the cell format widget. That's the obvious place to look for it. I would like to be able to fix the date convention after pasting, if I find that it is displaying the wrong date.
Or maybe you could reproduce the same steps I take manually to validate the result of the paste. What I'm doing now, so I paste correctly: 1) copy from one gnumeric file, paste in a text editor (leafpad); 2) copy from leafpad, paste to another gnumeric file; 3) gnumeric opens the import widget, and I select date, and the format I want; 4) the paste is successful. If you include the convention in the clipboard data, then gnumeric should take into account the convention of the file it is pasting to, right? And convert the data accordingly. If more validation is required when gnumeric pastes that data back, it could check if the result to be displayed resembles what would be displayed in the source convention format. Else, just paste what it looks like and ask the user to supply the cell data format.
*** Bug 525323 has been marked as a duplicate of this bug. ***
Created attachment 108942 [details] [review] Patch for the brave
The src/wbc-gtk-actions.c part is irrelevant for this bug.
Awesome! The patch works here. But I think you made two typos: "Copying between sheet with different date convensions It is possible that some dates could be be copied correctly." You probably meant "conventions" and "incorrectly", right? Because I can just be glad if the dates are copied correctly. No need to warn me if it works as expected! ;-)
Another typo: it should read "sheets" (plural).
This problem has been fixed in our software repository. The fix will go into the next software release. Thank you for your bug report.
commands.c now has: "It is possible that some dates could be be copied" (note the double "be").
Fixed.