Excel date conversion

Is there a way to convert the excel date number i.e. 34516 into a
datestring: 01-Jul-94

Thanks

On Mar 5, 9:38 am, WKC CCC [email protected] wrote:

Is there a way to convert the excel date number i.e. 34516 into a
datestring: 01-Jul-94

Thanks


Posted viahttp://www.ruby-forum.com/.

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

Thanks, but I am not interfacing through the Excel com object.

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 B. http://agileconsultingllc.com
[email protected]

On 3/5/07, WKC CCC [email protected] 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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs