[Octave-bug-tracker] [bug #62246] [octave forge](io) xlsread miscalculates serial date number using UNO interface

[Philip Nienhuis]

Update of bug #62246 (project octave):

                  Status:               Need Info => In Progress            

    _______________________________________________________

Follow-up Comment #3:

Thanks for reporting back, and yeah what you describe is what I suspected.

At issue is that the old Excel .xls (BIFF8 and older BIFF5) formats don't
support dates internally. Only doubles, strings, booleans and maybe formulas
are supported natively, all other "types" are distinguished by the cell
*formats*. Date and time values belong to those.

So you and/or your colleagues used an .xls file where internally the date
values are based to be based upon Excel's old BIFF epoch of 31-12-1899 - *if*
they were formatted as dates, that is.
Now, LibreOffice *may* recognize that and transform it into .ods dates with
epoch 1-1-0000, but that doesn't always happen reliably. Are you sure the .xls
file with which it all started had those date value cells explicitly formatted
as dates?

FYI, in the io package there is an .html document (in <io package install
dir>/doc/ where somewhere under the title "MATLAB COMPATIBILITY AND SOME
GOTCHAS" you can read this:
> ...
_Note that you can get an interesting confusing with regard to dates when
reading .ods files and then writing them to disk as .xls or .xlsx files, or
vice versa._
...
These days .xlsx (OOXML) support in LibreOffice and Excel itself is much more
reliable than when this stanza was written but using .xls / BIFF in workflows
still remains a PITA.

I'll have a look if I can reproduce this bug report's issue with the file you
uploaded, but I'm afraid the collection of software, let alone mix of
operating systems, used to create and process the file is such that I have
little hope if I can make a difference.

At the moment my best advice would be to just be wary of epoch issues in
spreadsheet files and thoroughly check them if .xls (BIFF8 or BIFF5) is used
somewhere in your workflow.
It might help if you can also upload or in some other way share the original
.xls file, but I'd understand if that would be undesired for your company.



    _______________________________________________________

Reply to this item at:

  <https://savannah.gnu.org/bugs/?62246>

_______________________________________________
  Message sent via Savannah
  https://savannah.gnu.org/