Calc Paste of dates changes the contents

English
, , ,
#1

I’m having a baffling problem. I have several different spreadsheets set up in LibreOffice under Mac OS X 10.6.8

On one of them one of the columns is set to be a date in format MM/DD/YYYY

I want to copy the data from that column into another column in another spreadsheet document.
I have selected that column and set the format of the cells to be the same date format MM/DD/YYYY

In the first spreadsheet I select the cells containing the dates I want.
copy (command c)
move to the spreadsheet where I want to paste them
select the cells where they are to be pasted
paste (command v) and the data is entered as a date but they have all changed what was 05/08/2005 in one sheep has become 05/09/2001 in the second sheet!

This happens no matter what combinations of pasting a single cell or group of cells or any paste special options I have tried.
I have verified that in the paste I am not doing any operations on the data.

If I select a single cell in the first spreadsheet. highlight the actual text entry in the upper toolbar, copy that text, then paste that it works as expected and the date is correct. I cannot go in and by hand copy each individual cell that way because there are hundreds of these to do.

Why is the paste changing my data and how do I fix it?

#2

Could you please answer your question and add these two files you are talking about. Or can you reproduce this with new completely empty documents?

#5

I was able to repeat the issue with smaller documents. I even converted the dates to integers, compared the numbers and then pasted those accurate numbers into a new spreadsheet and the dates would be wrong. The final reason relates to the date and how it is stored and calculated in various versions. Libre Office standard is to have the start date be 12/30/1899 Excel uses 1/1/1900 and Mac systems use 1/1/1904. My base spreadsheet was using 1/1/1904 as the base date and I was trying to paste into a spreadsheet that was using 12/30/1899. You can set the base date on the Mac version by selecting LibreOffice, Preferences, LibreOffice Calc, Calculate and then choosing the base date over on the right hand side. Verify all dates if you round trip to and from Excel systems, especially if they are on PCs.

#6

And I can’t mark this as the correct answer because I don’t have enough points to do so.

#3

Have you selected the same language in both spreadsheet?
Menu/Tools/Options/Language settings - Local settings.

#4

Yes same language in both spreadsheets.

#7

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.