base date malfunction when pasting dates from an opened .csv file

migolio · March 24, 2021, 1:13pm

I have a Libre Office calc document (documentX.ods) where from time to time I paste calc date fields obtained from a documentY.csv file. When I open the .csv file directly from the Windows 10 operating system, Windows has the association between the .csv extension and Libre Office and opens Libre Office. When I copy a range of date fields from the opened documentY.csv file to the the other documentX.ods file, the dates are wrongly pasted, with years of difference.

I noticed that the base date in the documentX.ods file ( menu→tools→options→ Libre Office Calc→ Calculate → Date) is 12/30/1899 (the default date). The base date in the opened documentY.csv file is 01/01/1904. This explains why I get wrongly pasted values in documentX.ods.

But… if I open the documentY.csv differently, i.e. I create first a new calc document from Libre Office and then open documentY.csv from there, I get as base date the default 12/30/1899 date! For some unknown reason Libre Office when started from Windows via the association with the .csv file uses as base date 01/01/1904 instead of the default. If I force a change to the default date of 12/30/1899 and apply the change, it refuses and returns to the 01/01/1904 date!

This anomaly happens with both LibreOffice_7.0.3_Win_x64 and LibreOffice_7.1.1_Win_x64.

Does anyone know how to avoid this anomaly other than avoiding opening the .csv file directly from Windows?

mikekaganski · March 24, 2021, 1:38pm

Do you have a default custom template for spreadsheets, that defines that 1899-12-30, and otherwise have the general setting changed in your profile?

Lupp · March 24, 2021, 3:35pm

The different settings concerning the base-date (“day zero”, .NullDate) are dangerous, and the only halfway reasonable one (StarOffice 1.0) is no longer used anywhere. Since ODF spreadsheets have no actual data type for dates and date-time-stamps, and the number-format type (2 through 7) is no safe surrogate, Calc should seriously warn if a copy/paste operation between documents can badly lead to re-interpreted date values with an offset of 1462 days.
Users exchanging dates (or date-containing information) via different means like csv-files should never use a numeric representation dependening on such arbitrary settings, but exclusively textual representations conforming to ISO 8601.
Regarding the hint by @mikekaganski, information from a .csv should then not be obtained by opening the .csv with Calc (and subsequent Copy/Paste) for which process a default template with a probably corrupted setting would be used, but by importing it into the target document directly.

mikekaganski · March 24, 2021, 4:26pm

If I force a change to the default date of 12/30/1899 and apply the change, it refuses and returns to the 01/01/1904 date!

Please try the following:

Create a new Calc document
Change the setting at Options|Calc|Calculate
Close the Calc file without saving

and check is this helps.

If that fails, then make sure that you do not use any custom template under File|Templates|Manage Templates, and repeat the procedure.

Lupp · March 24, 2021, 4:32pm

@mikekaganski: Just tried the following: Entered a few dates and date-time values into a single-sheet document set to zero-date 1904-01-01, Then copied the sheet using Move/Copy to a multisheet document with day-zero 1899-12-20. The dates were re-intrepreted with the mentioned offset without any alert or notice.