Ask Your Question

Calc Paste of dates changes the contents [closed]

asked 2013-05-12 21:37:24 +0200

OogieM gravatar image

updated 2015-11-01 20:05:26 +0200

Alex Kemp gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-01 20:07:10.342240


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?

mahfiaz gravatar imagemahfiaz ( 2013-05-12 21:42:28 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-05-23 14:20:12 +0200

OogieM gravatar image

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.

edit flag offensive delete link more



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

OogieM gravatar imageOogieM ( 2013-05-23 14:22:55 +0200 )edit

answered 2013-05-12 23:52:09 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more


Yes same language in both spreadsheets.

OogieM gravatar imageOogieM ( 2013-05-23 14:20:51 +0200 )edit

Question Tools


Asked: 2013-05-12 21:37:24 +0200

Seen: 1,850 times

Last updated: May 23 '13