Asp Forum
Home
|
Login
|
Register
|
Search
Forums
>
comp.lang.ruby
Excel date conversion
WKC CCC
3/5/2007 2:38:00 PM
Is there a way to convert the excel date number i.e. 34516 into a
datestring: 01-Jul-94
Thanks
--
Posted via
http://www.ruby-...
.
4 Answers
David Mullet
3/5/2007 4:56:00 PM
0
On Mar 5, 9:38 am, WKC CCC <wai-kee.ch...@uk.bnpparibas.com> wrote:
> Is there a way to convert the excel date number i.e. 34516 into a
> datestring: 01-Jul-94
>
> Thanks
>
> --
> Posted via
http://www.ruby-...
.
Here's something to keep in mind (if you don't already): A cell in
Excel has a Value property and a Text property. The Value property
returns the underlying data as Excel has stored it. The Text property
returns the formatted text as displayed to the user.
irb(main):010:0> ws.Cells(3, 1).Value
=> "1994/07/01 00:00:00"
irb(main):011:0> ws.Cells(3, 1).Text
=> "01-Jul-94"
So, if the cell is already formatted as you wish, just call the Text
property rather than the Value property.
Hope that helps.
David
WKC CCC
3/5/2007 5:06:00 PM
0
Thanks, but I am not interfacing through the Excel com object.
--
Posted via
http://www.ruby-...
.
Rob Biedenharn
3/5/2007 6:12:00 PM
0
On Mar 5, 2007, at 9:38 AM, WKC CCC wrote:
> Is there a way to convert the excel date number i.e. 34516 into a
> datestring: 01-Jul-94
>
> Thanks
>> require 'data'
=> true
>> class Numeric
>> def to_excel_date
>> Date.new(1899,12,30) + self.to_i
>> end
>> end
=> nil
>> 34516.to_excel_date
=> #<Date: 4899069/2,0,2299161>
>> 34516.to_excel_date.strftime("%d-%b-%y")
=> "01-Jul-94"
If that's not quite right, the trick is to find out what date Excel
says is 0 and add that julian date (Date#jd) to the value you want
and then you manipulate the Ruby Data object as needed.
-Rob
Rob Biedenharn
http://agileconsult...
Rob@AgileConsultingLLC.com
Hannes Wyss
3/6/2007 8:23:00 AM
0
On 3/5/07, WKC CCC <wai-kee.chung@uk.bnpparibas.com> wrote:
> Thanks, but I am not interfacing through the Excel com object.
assuming that you're using ParseExcel:
cell.date.strftime("%d-%b-%y")
hth
Hannes
Servizio di avviso nuovi messaggi
Ricevi direttamente nella tua mail i nuovi messaggi per
Excel date conversion
Inserendo la tua e-mail nella casella sotto, riceverai un avviso tramite posta elettronica ogni volta che il motore di ricerca troverà un nuovo messaggio per te
Il servizio è completamente GRATUITO!
x
Login to ForumsZone
Login with Google
Login with E-Mail & Password