Is there any code or an article that lists exactly how to convert to/from a
datetime as stored in an Excel XLSX file and
year/minth/day/hour/minute/second?

Also, from playing with it, it appears that Excel thinks 2/29/1900 existed
(it was not a leap yer).

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

Not to sure what you mean when you say convert to from date. Convert to what?
You can use the text function to get a date converted to text.

=Text(A1, "yyyy/mm/dd/hh/mm/ss")

As for the leap year issue that is not a bug in XL. It is a bug in Lotus
123. For XL to be compatable it had to make the same mistake as Lotus.
--
HTH...

Jim Thomlinson


"David Thielen" wrote:

> Is there any code or an article that lists exactly how to convert to/from a
> datetime as stored in an Excel XLSX file and
> year/minth/day/hour/minute/second?
>
> Also, from playing with it, it appears that Excel thinks 2/29/1900 existed
> (it was not a leap yer).
>
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>

Now that's what I call being serious about compatibility

I have some C# code that parses an XLSX file. So it reads that the cell is
formatted as a date_time and has a value of 12345.678 - I need to convert
that to year, month, day, hour, second, minute to create a DateTime object.

And then I have to do the reverse when creating an XLSX output file.

One thing I have learned about dates - it is very very difficult to get it
right (Lotus 123 being a good example). So if there is tested code that does
this somewhere, it would be very helpful.

Especially if there are other purposful errors in addition to the 2/29/1900
one.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"Jim Thomlinson" wrote:

> Not to sure what you mean when you say convert to from date. Convert to what?
> You can use the text function to get a date converted to text.
>
> =Text(A1, "yyyy/mm/dd/hh/mm/ss")
>
> As for the leap year issue that is not a bug in XL. It is a bug in Lotus
> 123. For XL to be compatable it had to make the same mistake as Lotus.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "David Thielen" wrote:
>
> > Is there any code or an article that lists exactly how to convert to/from a
> > datetime as stored in an Excel XLSX file and
> > year/minth/day/hour/minute/second?
> >
> > Also, from playing with it, it appears that Excel thinks 2/29/1900 existed
> > (it was not a leap yer).
> >
> > --
> > thanks - dave
> > david_at_windward_dot_net
> > http://www.windwardreports.com
> >
> > Cubicle Wars - http://www.windwardreports.com/film.htm
> >
> >

On Thu, 3 Apr 2008 16:54:00 -0700, David Thielen <(E-Mail Removed)> wrote:

>Now that's what I call being serious about compatibility
>
>I have some C# code that parses an XLSX file. So it reads that the cell is
>formatted as a date_time and has a value of 12345.678 - I need to convert
>that to year, month, day, hour, second, minute to create a DateTime object.
>
>And then I have to do the reverse when creating an XLSX output file.
>
>One thing I have learned about dates - it is very very difficult to get it
>right (Lotus 123 being a good example). So if there is tested code that does
>this somewhere, it would be very helpful.
>
>Especially if there are other purposful errors in addition to the 2/29/1900
>one.

I don't know about "tested code", but the conversion should be pretty
straightforward, including adding in a day for February 29, 1900. (I have read
that this mistake started with Lotus 1-2-3, which predated Excel, and that MS
incorporated this error into Excel for "compatibility".

Excel stores dates/times as days and fractions of a day. If the worksheet was
using the 1900 date system, then 1 = 1 Jan 1900. And 12 noon on that day would
be represented as 1.5.

If using the 1904 date system, then 1 = 2 Jan 1904 (0 = 1 Jan 1904).

(Date1904 is a Workbook property, but I don't know how that would be stored in
the XLSX file).
--ron

On Thu, 3 Apr 2008 16:54:00 -0700, David Thielen <(E-Mail Removed)> wrote:

>Now that's what I call being serious about compatibility
>
>I have some C# code that parses an XLSX file. So it reads that the cell is
>formatted as a date_time and has a value of 12345.678 - I need to convert
>that to year, month, day, hour, second, minute to create a DateTime object.
>
>And then I have to do the reverse when creating an XLSX output file.
>
>One thing I have learned about dates - it is very very difficult to get it
>right (Lotus 123 being a good example). So if there is tested code that does
>this somewhere, it would be very helpful.
>
>Especially if there are other purposful errors in addition to the 2/29/1900
>one.

I forgot to mention that, within VBA, CDate will convert your value to a date,
but the VBA date system does not include Feb 29, 1900

In the Immediate Window:

?cdate(12345.678)
10/18/1933 4:16:19 PM

Since the VBA date system does not include Feb 29, 1900, it starts off with

1 = 12/31/1899

So there is a one day difference until Mar 1, 1900 compared with Excel; then
they agree.
--ron