I am testing roo and I have a problem reading xlsx files that contain
date formats like '12-apr-11'. When I change this to '12-04-11' it
works fine. But for the format I need, roo always returns a float
instead of a date.
The problem is that I cannot change these files manually to a format
that roo recognizes.
Is there a way to adapt the date formats roo recognizes?
I found a way to fix this problem. The float value that roo returns
seems to correspond with the internal float value that Excel is using
for dates. The float value is the number of days since 1 January 1900.
But for some reason you need to subtract 2 days to get it correct. See
also http://www.ozgrid.com/Excel/ExcelDateandTimes.htm (there you can
read that MS counts 29 Feb 1900, but that date does not exist, what
partly explains the subtraction).
Hope this is helpful for others having problems with dates.
Regards,
Pascal
On 30 apr, 16:23, PAV <pascal.verlin...@gmail.com> wrote:
> I am testing roo and I have a problem reading xlsx files that contain
> date formats like '12-apr-11'. When I change this to '12-04-11' it
> works fine. But for the format I need, roo always returns a float
> instead of a date.
> The problem is that I cannot change these files manually to a format
> that roo recognizes.
> Is there a way to adapt the date formats roo recognizes?
On 1 May 2011 15:01, PAV <pascal.verlin...@gmail.com> wrote:
> Hi,
> I found a way to fix this problem. The float value that roo returns > seems to correspond with the internal float value that Excel is using > for dates. The float value is the number of days since 1 January 1900. > But for some reason you need to subtract 2 days to get it correct. See > also http://www.ozgrid.com/Excel/ExcelDateandTimes.htm (there you can > read that MS counts 29 Feb 1900, but that date does not exist, what > partly explains the subtraction).
can you send me a spreadsheet file which causes this error? If it is a date field roo should also return a Date object.
> > I am testing roo and I have a problem reading xlsx files that contain > > date formats like '12-apr-11'. When I change this to '12-04-11' it > > works fine. But for the format I need, roo always returns a float > > instead of a date. > > The problem is that I cannot change these files manually to a format > > that roo recognizes. > > Is there a way to adapt the date formats roo recognizes?
> > Many thanks for your help!
> > Pascal
> -- > You received this message because you are subscribed to the Google Groups > "ruby-roo" group. > To post to this group, send email to ruby-roo@googlegroups.com. > To unsubscribe from this group, send email to > ruby-roo+unsubscribe@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/ruby-roo?hl=en.
> On 1 May 2011 15:01, PAV <pascal.verlin...@gmail.com> wrote:
> > Hi,
> > I found a way to fix this problem. The float value that roo returns
> > seems to correspond with the internal float value that Excel is using
> > for dates. The float value is the number of days since 1 January 1900.
> > But for some reason you need to subtract 2 days to get it correct. See
> > alsohttp://www.ozgrid.com/Excel/ExcelDateandTimes.htm(there you can
> > read that MS counts 29 Feb 1900, but that date does not exist, what
> > partly explains the subtraction).
> can you send me a spreadsheet file which causes this error? If it is a date
> field roo should also return a Date object.
> -Thomas
> > Hope this is helpful for others having problems with dates.
> > > I am testing roo and I have a problem reading xlsx files that contain
> > > date formats like '12-apr-11'. When I change this to '12-04-11' it
> > > works fine. But for the format I need, roo always returns a float
> > > instead of a date.
> > > The problem is that I cannot change these files manually to a format
> > > that roo recognizes.
> > > Is there a way to adapt the date formats roo recognizes?
> > > Many thanks for your help!
> > > Pascal
> > --
> > You received this message because you are subscribed to the Google Groups
> > "ruby-roo" group.
> > To post to this group, send email to ruby-roo@googlegroups.com.
> > To unsubscribe from this group, send email to
> > ruby-roo+unsubscribe@googlegroups.com.
> > For more options, visit this group at
> >http://groups.google.com/group/ruby-roo?hl=en.
On 2011-05-21, PAV <pascal.verlin...@gmail.com> wrote:
>> > I found a way to fix this problem. The float value that roo returns >> > seems to correspond with the internal float value that Excel is using >> > for dates. The float value is the number of days since 1 January 1900. >> > But for some reason you need to subtract 2 days to get it correct. See >> > alsohttp://www.ozgrid.com/Excel/ExcelDateandTimes.htm(there you can >> > read that MS counts 29 Feb 1900, but that date does not exist, what >> > partly explains the subtraction).
>> can you send me a spreadsheet file which causes this error? If it is a >> date >> field roo should also return a Date object.
I have fixed this problem. Replace the excelx.rb file in your gem with the attached file.
> On 2011-05-21, PAV <pascal.verlin...@gmail.com> wrote:
> >> > I found a way to fix this problem. The float value that roo returns
> >> > seems to correspond with the internal float value that Excel is using
> >> > for dates. The float value is the number of days since 1 January 1900.
> >> > But for some reason you need to subtract 2 days to get it correct. See
> >> > alsohttp://www.ozgrid.com/Excel/ExcelDateandTimes.htm(thereyou can
> >> > read that MS counts 29 Feb 1900, but that date does not exist, what
> >> > partly explains the subtraction).
> >> can you send me a spreadsheet file which causes this error? If it is a
> >> date
> >> field roo should also return a Date object.
> I have fixed this problem. Replace the excelx.rb file in your gem with
> the attached file.
