Excel 2007 export/import datetime

[KWarner]

I have an xmlMap with a datetime field mapped to a listColumn, named "Time",
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 one 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 my vba 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 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 search for
a better answer.

 

[Wouter HM]

Hi KWarner,

The time peiode 04:00 - 27:59 looks odd, but you might need it.
From the top of my head I suggest just before exporting to XML
subtract 4 hours from the values in the Time column.
After export add those 4 hours.

Also after imprting the values add 4 hours to these impoted values.

HTH,

Wouter

 

[KWarner]

Wouter HM, thanks for your time.
27:59 = 1/1/1900 3:59 AM - our workday goes from 4:00 AM to 3:59 AM. I
can't just have 1:00 AM be 1:00 AM because then it doesn't sort correctly -
1:00 AM needs to come after 11:00 PM. Therefore, 1:00 AM has to be 25:00.

My xsd definition has the "Time" column as dateTime. So, when a time-only
value gets exported (i.e. 16:47) it tacks the date on like this:
"<Time>1899-12-31T16:47:00.000</Time>". Excel doesn't like the year 1899,
so I am unable to import this as dateTime, it actually gets imported as text.
I could add a day to all records before export and then subtract a day after
import, but there are over 2000 records. This is just another kludgy
workaround and my other kludgy workaround is faster, even though using 1904
date system is generally a bad idea.

 

[KWarner]

I changed the "Time" column from dateTime to double and all is right with the
world.

 

[joel]

I hate working with data and times in VBA because it is very clumbsy.
I often have to do work around like you are doing. One thing tha
bothers me is your statement that VBA doesn't recognize time afte
24:00. Since any time greater than 24 hoursa should be listed as Jan 2
From my experience what I found is if you declare your varaible as tim
VBA truncates the time to 24 hours. But is you declare the variables a
single or double VBA will not truncate. I often have to switch betwee
variables declared as single and time to get my VBA code to wor
properly.