Hi everybody,
i’ve found a little problem that results in a big incompatibility between libreoffice calc and microsoft excel.
When you write a number in a cell and display the cell with a date format, the result is displayed adding the number you give to a so called date zero.
In libreoffice calc the date zero is “12/30/1899” or “01/01/1904”
but
in microsoft excel 2003 the default zero date “12/31/1899”.
So, if i write in a cell the number 10 and display the cell with a format “M/G” i obtain with calc “1/9” while in excel in displays “1/10”.
I know that i should have posted in some libreoffice forum but i think that opensuse’s libreoffice implementation is the best around, better than the original.
Hope someone helps.
Thanks in advance
This is a well known bug which has been documented by Microsoft. To quote from Microsoft’s own documentation:
Excel supports two date systems: the 1900 and 1904 date systems. Thedefault date system for Microsoft Excel for Windows is 1900. Thedefault date system for Microsoft Excel for the Macintosh is 1904.You can change the date system. On the Tools menu, clickOptions, click the Calculation tab, and then select orclear the 1904 date system check box.
Each system numbers days from 1 and there is a leap year error in one which complicates things. But that is the origin of the discrepancy.
On 2014-01-17 22:56, john hudson wrote:
>
> This is a well known bug which has been documented by Microsoft. To
> quote from Microsoft’s own documentation:
>
>> Excel supports two date systems: the 1900 and 1904 date systems.
>> Thedefault date system for Microsoft Excel for Windows is 1900.
>> Thedefault date system for Microsoft Excel for the Macintosh is 1904.You
>> can change the date system. On the Tools menu, clickOptions, click
>> the Calculation tab, and then select orclear the 1904 date system
>> check box.
>
> Each system numbers days from 1 and there is a leap year error in one
> which complicates things. But that is the origin of the discrepancy.
And you can adjust for that in LibreOffice, too.
LO - Calc, Tools, Options.
There, select LibreOffice Calc, Calculate
Date:
12/30/1899 (default)
01/01/1900 (StarCalc 1.0)
01/01/1904
–
Cheers / Saludos,
Carlos E. R.
(from 12.3 x86_64 “Dartmouth” at Telcontar)