Excel 2007 export/import datetime
KWarner
formatted as "[hh]:mm".
My app requires that the "Time" column be in the range "04:00" - "27:59" -
no date. Obviously, anything after midnight will have a date of 1/1/1900.
I export a workbook using this map to an xml file and the xml file says:
<Time>1899-12-31T13:00:00.000</Time>
or
<Time>1900-01-01T02:00:00.000</Time>
When I try to import the xml file into a different workbook the 1899 date is
an invalid date, so it imports it as text.
My workaround is to make both of these workbooks use 1904 date system. I
know, bad idea. But, I am the only one using these 2 workbooks (at least
until I die or retire) and they don't reference other workbooks. So, I figure
I am relatively safe for the moment.
This works fine until I do listColumn("Time").find(time) on a time greater
than 23:59. VBA doesn't recognize anything after "23:59" as a valid date, so
I have to subtract 24 from the string and then do a DateAdd("d",1,time)
function. Unfortunately, DateAdd doesn't know that my workbook is using the
1904 date system, so I have had to create my own AddADay function.
This all works, but I hate kludgy workarounds, so I continue to look for a
better answer.