Background

I recently received an Excel spreadsheet (.xlsx) file that included dates for holidays for 2013 as my office. Dates that should’ve been displayed as 2013 dates were being displayed as 2016. I’m using a pretty recent version of LibreOffice, 3.6.0.4 (Build ID: 932b512), having just installed it on Aug 12, 2012.

bad dates

Solution

I hadn’t run into this particular issue before but a bit of googling lead me to believe that it was potentially one of two things.

1. The .libreoffice directory in my home dir was somehow corrupt and/or tainted. It was pretty old, most likely getting created while using a previous version of LibreOffice, maybe 3.2, and I was just letting it get migrated automagically when using each progressive version.
2. The Date setting was incorrectly set under Tools -> Options -> LibreOffice Calc -> Calculate.

Sure enough, when I opened up the Calculate section under the Options dialog, I was using 01/01/1904 as my base date and not 12/30/1899, the default.

caclc base date options

Changing the Date radio button to the default choice and magically my dates were being displayed correctly.

good dates

Looking through LibreOffice’s Help file under Date and Time Functions, turned this useful information up.

Date base for day zero

Dates are calculated as offsets from a starting day zero. You can set the day zero to be one of the following:

’30/12/1899′ (default)
’01/01/1900′ (used in former StarCalc 1.0)
’01/01/1904′ (used in Apple software)

Choose Tools – Options – OpenOffice.org Calc – Calculate to select the date base.

When you copy and paste cells containing date values between different spreadsheets, both spreadsheet documents must be set to the same date base. If date bases differ, the displayed date values will change!

References

links

• Calc changing my dates – openoffice.org community forums

NOTE: For further details regarding my one-liner blog posts, check out my one-liner style guide primer.