Whenever I enter dates and save my spreadsheet in calc it changes the date automatically by subtracting one day.
For eg. If I enter 01-01-1975 , when I save and open it is shown as 31-12-1974
This happens for all dates in different spreadsheets.
Please advise. Thanks in advance.
87573.ods (18.7 KB)
contains this year’s dates from 2023-01-01 until 2023-02-06. Same problem with this file?
Please create a new Calc document and see the parameter:
Menu / Tools / Options / LibreOffice Calc / Calculate, field Date.
What is your value?
This may be connected to the error in Excel date handling. Are your spreadsheets saved to Excel file format or otherwise " touched by Excel"?
1 Like
Hi,
Thanks for reply.
The value Selected is 01/01/1900 ( StarCalc 1.0)
The other options given are
12/30/1899 ( default) and
01/01/1904
Hi,
Thanks for your reply.
The files were created natively on my desktop using Calc. and there is no MS Office installed. So it is not " touched by Excel" 
Hi,
Thanks for your reply.
No my issue is not similar to what you have sent.
That’s the culprit. For any document you create it should be 12/30/1899 ( default) instead, unless you have a very compelling reason to pick another null-date.
Herein lies the error, I suspect.
Explanation
AFAIK, the other options are for spreadsheets originating from the original StarOffice Calc module some 35-40 years ago (the 1900 date), and for spreadsheets originating from (or adapted to) old Apple products (the 1904 date).
It still should not alter dates in spreadsheets saved as ODS files and then reopened. Even mishandling Excel files with this setting should be considered a bug, but perhaps not possible to get right with that storage format. If you really didn’t “Save as Excel file” from Calc, I suspect a more serious bug. Such a bug may be easily missed with this specific, rarely needed adjustment. Cf. also comment from @mikekaganski on this. To determine whether a bug exists in your version, please state the version of your installed LibreOfice suite (menu item Help - About…) and which operating system you are using.
Solution
Change the base date (aka “epoch”) to 12/30/1899. This may seem strange, but that specific date is chosen in order to circumvent the aforementioned “Excel error” with least possible impact to existing spreadsheets.
Background
Excel currently is the de facto standard for spreadsheet apps, so optimizing compatibility with Excel data makes sense. IIRC it inherited this error from Lotus 123 which was the previous industry standard of sorts, which again inherited it from Visicalc, the “mother of all spreadsheet apps”.
1 Like
Still, if the files are stored as ODS, they should not change dates after reload.
1 Like
Thanks for the suggestion. Looks like I have saved a few files in .xlsx format. and many in the .ods format. But this error shows up in all files.
I changed the base date as suggested. closed and opened the file. The dates have not been changed, So I suppose this is the solution to the problem.
Thanks for taking the time to explain it.
Since the zero day is stored in the document, it should never change because the difference between the entered day and the zero day remains the same. When there is no explicit “NullDate” setting, 1899-12-30 applies by default.
There used to be an old OpenOffice issue with a “NullDate” stored in registrymodifications.xml. When this entry existed any file with no explicit “NullDate” setting inherited this value. Any new file worked with 1899-12-30 and after saving this default was overridden by the NullDate stored in registrymodifications.xml.
2 Likes