bugGNU Octave - Bugs: bug #53401, [octave forge] (io) dates from...

 
 

bug #53401: [octave forge] (io) dates from xlsread vs. odsread

Submitter:  Muhali <muhali>
Submitted:  Wed 21 Mar 2018 01:45:12 PM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  None
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  Open/Closed:  * Closed
Release:  * dev Operating System:  * Any
Fixed Release:  None Planned Release:  None
* Mandatory Fields

Add a New Comment Rich Markup
   

Jump to the original submission

Fri 23 Mar 2018 08:45:50 PM UTC, comment #7: 

Indeed, when reading .ods date cells 693960 was added to an already correct datenum, fixed here:
http://hg.code.sf.net/p/octave/io/rev/37fd8bda5423

However, reading .xlsx is correct. LibreOffice correctly converts its epoch 1-1-0000 to Excels' 1-1-1900, so Jan 1, 1900 gets to be 2, taking into account the LO "bug" mentioned in the discussion below. Any date after Feb 28, 1900 is returned as a correct Excel date value.
As this is an upstream bug I won't fix it in the io package.

I think an io-2.4.11 bug fix release incl. this fix will be out before Octave-4.4.0 will be released.

Closing report.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 23 Mar 2018 04:54:48 PM UTC, comment #6: 

@Muhali:  
Just occurred to me:
The LO bug is probably related to the infamous Excel bug that Excel incorrectly thinks 1900 is a leap year.
I guess LO folks thought that the first -what is it- 59 days of 1900 wouldn't matter compared to the rest of the 20th century and 21th up til now.

I'll attach a pic of Excel and LO side-by-side showing the same .xslx file. Starting in March 1900 the dates are in sync.



Philip Nienhuis <philipnienhuis>
Group Member
Thu 22 Mar 2018 07:33:49 PM UTC, comment #5: 

LO bug filed here:
https://bugs.documentfoundation.org/show_bug.cgi?id=116572

As to the actual bug here, I've got only time for some minor bug triaging until the end of next week. Thx for reporting

Philip Nienhuis <philipnienhuis>
Group Member
Thu 22 Mar 2018 01:13:18 PM UTC, comment #4: 

Confirmed with Excel and the current LO-dev.

Muhali <muhali>
Thu 22 Mar 2018 08:34:06 AM UTC, comment #3: 

Not yet.
First I'll investigate further, and I have no time now.

Have you confirmed? oh but you probably don't have Excel, do you?

Philip Nienhuis <philipnienhuis>
Group Member
Thu 22 Mar 2018 08:11:18 AM UTC, comment #2: 

Philip, are you filing a bug report for Libreoffice?

Muhali <muhali>
Wed 21 Mar 2018 08:35:38 PM UTC, comment #1: 

No, you saw it right assuming you used the OCT interface.
I'll look into it.
At first sight it looks like the 693960 term is added twice.

LibreOffice 5.4.1.2 screws up as well:
Writing 31/12/1899 and 1/1/1900 (European nation, DMY) into an .xlsx is read by Excel as 1/1/1900 and 2/1/1900. So it looks LibreOffice is off-by-one there.  With .ods it seems fine.

Philip Nienhuis <philipnienhuis>
Group Member
Wed 21 Mar 2018 01:45:12 PM UTC, original submission:  

I have entered the date 1900-01-01 into Libreoffice Calc, and saved it to both foo.ods and foo.xlsx. With octave I get the following:

octave:8> ODS = odsread("foo.ods")
ODS = 1387922
octave:9> XLS = xlsread("foo.xlsx")
XLS = 2

According to the documentation, ods has a base date of 0000-01-01 and xlsx one of 1900-01-01. But

1. The value of ODS does not equal 693962 = datenum(1900,1,1).
2. The value of XLS does not equal 1.


Am I on a totally wrong track here?

Muhali <muhali>

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

Attached Files

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by mtmiller (Updated the item)
  • -email is unavailable- added by muhali (Submitted the item)
  •  

    There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

    Only group members can vote.

     

    Follow 7 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2018-03-23 philipnienhuis Open/ClosedOpen Closed
    2018-03-23 philipnienhuis StatusConfirmed Fixed
        Assigned toNone philipnienhuis
    2018-03-23 philipnienhuis Attached File- Added Excel_vs_LO_1900.PNG, #43651
    2018-03-21 mtmiller Summaryio: dates from xlsread vs. odsread [octave forge] (io) dates from xlsread vs. odsread
    2018-03-21 philipnienhuis StatusNone Confirmed
        Operating SystemGNU/Linux Any

    Back to the top

    Powered by Savane 3.13-02a9.
    Corresponding source code