LibreOffice Calc copy/paste dates may be 4 years wrong

I recently came upon a very strange bug, or should I call it “behaviour” when doing copy-paste between LibreOffice spreadsheets. What was extremely annoying was that a date like 2013-04-09 would transform to 2009-03-09. I suspected something was wrong but had no idea what until I saw a long conversation about a similar behaviour in a bug report [bugs.freedesktop.org].

Take a look at one of the settings in LibreOffice Calc:

libreoffice_calc_date

There it is: the source of the misbehaving copy/paste is the starting date used to calculate the actual date. Dates are numbers that start at a given base. If a spreadsheet was built on an older Excel on a Mac, it will retain the start of dates as 1904. Apparently, this date start was not automatically changed when importing these files in LibreOffice. If the spreadsheets starts counting in january 1904, any date copied and pasted in another spreadsheet that starts counting in december 1899 will be shifted back in time by about 4 years (in fact 1462 days).

Mistery solved. Be careful – if you change the above settings in LibreOffice Calc, the dates of an open spreadsheet that had an incorrect base to start with will change immediately. To recover the old dates, just add 1462 (or a different number if your spreadsheet happened to use, for example, 01/01/1900 to start counting).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s