This is just a query, not a how to. I have some data which is exported to an XML file. When I import this into excel the time column appears to be unformatted ie: it is not formatted already as hh:mm:ss, since if it was, then when I convert that to a decimal it should give me the time value (in seconds) expressed as a fraction of 24hrs.
A quick search of XML data types reveals that if you don't specify a data type then excel imports the data as text. So this is what it looks like my XML file is doing ie: the time column is unspecified as opposed to xsd:time which I believe should be recognised by excel as hh:mm:ss
However, when I multiply by 86400 I still get the correct number in seconds. So how does excel know that the time column which it thinks is text, is actually time and then give me the right answer when I convert to seconds (and format as decimal)? I'm impressed!!
A quick search of XML data types reveals that if you don't specify a data type then excel imports the data as text. So this is what it looks like my XML file is doing ie: the time column is unspecified as opposed to xsd:time which I believe should be recognised by excel as hh:mm:ss
However, when I multiply by 86400 I still get the correct number in seconds. So how does excel know that the time column which it thinks is text, is actually time and then give me the right answer when I convert to seconds (and format as decimal)? I'm impressed!!