How to convert Microsoft times into Ruby DateTime

[William Hertling]

Hi all,

I've been going crazy trying to figure out a Microsoft time format
without any luck. Even Google seems to be no help with this.

In the source data I'm stuck with, the timestamps are of the form
"40076.185700". Now I know this is some valid Microsoft format,
because if I take that number, paste it into Excel, and format it as a
date, I get: "9/20/09 4:27 AM", which is what I would expect.

What I'm trying to do is parse these dates in the source format and
convert them into a Ruby DateTime object.

Has anyone seen this format before, and can you help me figure out how
to parse it into Ruby?

Thanks so much,
Will

[Sam Livingston-Gray]

There's at least one Excel gem; perhaps one of them contains some
clues on converting this?
-Sam

[Mike Gunderloy]

ruby-1.8.7-p174 > (DateTime.new(1899,12,30) + 40076.1857).to_s
=> "2009-09-20T04:27:24+00:00"

As to why Excel has that silly epoch date...that is a lesson in stupid
computing history ;)

Mike

[Markus]

> > In the source data I'm stuck with, the timestamps are of the form
> > "40076.185700". Now I know this is some valid Microsoft format,
> > because if I take that number, paste it into Excel, and format it as a
> > date, I get: "9/20/09 4:27 AM", which is what I would expect.
> >
> > What I'm trying to do is parse these dates in the source format and
> > convert them into a Ruby DateTime object.
> >
> > Has anyone seen this format before, and can you help me figure out how
> > to parse it into Ruby?

Put the numbers 0 and 1 into excel and format them as dates. Call the
results MS_date_0 and MS_date_1. Set these up as constants in ruby and
you should be able to write:

MS_date_0 + x*(MS_date_1 - MS_date_0)

To recover the date corresponding to some unknown date x.

-- Markus

[William Hertling]

Thanks all! I used Mike's approach and that worked for me.