date format support in roo

[PAV]

Hi,

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

[PAV]

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).

Hope this is helpful for others having problems with dates.

Regards,
Pascal

[Thomas Preymesser]

Hi Pascal,

On 1 May 2011 15:01, PAV <pascal.v...@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.

-Thomas
 

Hope this is helpful for others having problems with dates.

Regards,
Pascal

On 30 apr, 16:23, PAV <pascal.verlin...@gmail.com> wrote:
> Hi,
>
> 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...@googlegroups.com.
To unsubscribe from this group, send email to ruby-roo+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/ruby-roo?hl=en.

--
Thomas Preymesser
tho...@gmail.com
http://thopre.googlepages.com/
http://thopre.wordpress.com/

[PAV]

Hi Thomas,

I've send you a mail with a sample file today.

Best Regards,
Pascal

On 2 mei, 18:57, Thomas Preymesser <tho...@gmail.com> wrote:
> Hi Pascal,
>
> On 1 May 2011 15:01, PAV <pascal.v...@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

> thopre@gmail.comhttp://thopre.googlepages.com/http://thopre.wordpress.com/

[Thomas Preymesser]

Hello,

On 2011-05-21, PAV <pascal.v...@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.

-Thomas

[PAV]

Hi Thomas

Thanks a lot !

Best Regards,
Pascal

On 23 mei, 20:38, Thomas Preymesser <tho...@gmail.com> wrote:
> Hello,

>
> 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.
>
> -Thomas
>
> --
> Thomas Preymesser
> tho...@gmail.comhttp://thopre.googlepages.com/http://thopre.wordpress.com/
>

>  excelx.rb
> 25KWeergevenDownloaden