Dates returning as Fixnum about rubyxl HOT 9 CLOSED

The reason xlsx file return an int is because the type of the field is text, if in excel you change it to text you then see the integer, im running mac Excel 2011

from rubyxl.

I believe this appears similar to #31

from rubyxl.

Ah yes the 4000 error I have seen this in our prod environment as well and they have been fixing it with formatting the field to a date

from rubyxl.

Ugly hack but it works.

require 'rubyXL'
workbook = RubyXL::Parser.parse "workbook.xlsx"
worksheet = workbook.worksheet.first.get_table[:ẗable]
date_integer = worksheet.first["Date"]
=> 40981 # Number of days since Date.new(1900)
formatted_date = Date.new(1900) + date_integer
=> Thu, 15 Mar 2012

from rubyxl.

I have an example locally now seems to be when the the number format is not correctly determined to be a date excel stores dates as number hence the 4000 number if the format is not properly detected you can see the fixnum representation instead of the date hope to have a patch today

from rubyxl.

should be fixed in 1.2.10 so if it still broken comment on the ticket closing for now

from rubyxl.

I am using version 1.2.10 and I am still seeing the problem as described above.

The cell in my spreadsheet has a cell format of "Date". What might be more interesting to the developer is the more detailed configuration. When setting the cell format to "Date" you can also pick what "Type". When select a type beginning with a *, RubyXL doesn't recognize this as a date. If I choose a different one, such as "3/14/01" RubyXL does correctly identify it as a date. If you read further at the bottom of the screen when setting a "Date" format it reads.

"Date formats display date and time serial numbers as date values. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified for the operating system. Formats without an asterisk are not affected by operating system settings."

I would guess that in order to achieve this, excel is storing the value as an integer. Perhaps RubyXL isn't able to detect this slight difference.

from rubyxl.

formatted_date = Date.new(1900) + date_integer

..is out by two days. See here for a workaround:

http://www.databasically.com/2011/04/22/rubys-parseexcel-and-the-extra-date/

..so it should be:

Date.civil(1899, 12, 31) + date_integer - 1.day

from rubyxl.

@homezada: not sure what you are talking about. Can you provide a test case? Dates work as expected for me.

from rubyxl.