Results 1 to 3 of 3

Thread: Libreoffice calc date incompatibility with excel 2003

  1. #1

    Post Libreoffice calc date incompatibility with excel 2003

    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

  2. #2
    Join Date
    Jun 2008
    Location
    West Yorkshire, UK
    Posts
    3,417

    Default Re: Libreoffice calc date incompatibility with excel 2003

    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.

  3. #3
    Join Date
    Feb 2009
    Location
    Spain
    Posts
    25,547

    Default Re: Libreoffice calc date incompatibility with excel 2003

    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, click*Options*, 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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •