dates problem

[tyris]

hi
i do have an xml file like this :

<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?>
<?xml-stylesheet type=&quot;text/xsl&quot; href=&quot;C:\My Documents\excel.xsl&quot;?>
<results>
	<result>
		<field name=&quot;ASSIGNED TO&quot;>dupont</field>
		<field name=&quot;DTD_DONEDATE&quot;>02/27/2002 10:23:41 AM CET</field>
		<field name=&quot;CREATEDATE&quot;>07/17/2001 05:53:47 PM CEDT</field>
		<field name=&quot;DTD_DONETIME&quot;>0.15</field>
		....

i made an xslt that transforms this xml into an office HTML file that is understandable by Excel.
Into Excel the strings that represent dates an numbers are recognized as string, not number or dates...
i found a way to recognize a number this way :

<xsl:if test = &quot;string(number(.))!='NaN'&quot;>

is this case i simply add a label in html tag : "x:num" :

<td  class=&quot;xl2&quot; x:num><xsl:value-of select=&quot;.&quot;/></td>

Easy ! Excel even change the numeric separator depending on the regional setings GREAT.
Next Step : now i have to deal with dates... it's much more difficult (as you can see i can have different dates format : 07/17/2001 05:53:47 PM CEDT, 02/27/2002 03:01:20 PM CET)...
is there a way to recognize a date in a xsl code ? how can i specify it in office html so that excel recognize the date ?

Best regards,
Elise, XML learning girl X-)

 

[Guest_imported]

tyris,

I think your problem is more with Excel, but you can use the .xsl to solve your problem.

Excel doesn't interpret the CET, CEDT extensions on your date/time, so it won't turn them into a date/time for you. Essentially, you should convert them all to a standard time base.

You can actually accomplish this with either .xsl or within Excel, depending upon how you want to go about it. If you don't have the option of placing a macro on the users machine (the macro would be easier than the .xsl) than you'll need to convert the times using .xsl. While not impossible, it will be a little laborious. However, once you've done it right, you'll be able to use your .xsl conversion anywhere you have date formats such as this.

Brian

 

[chiph]

If you format your dates in ISO-8601 format (yyyy-mm-dd hh-nn-ss), then you should have a better chance of having Excel recognize them as dates. For more info, see:

http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Chip H.
Error on line 9: Object of type SIGNATURE expected

 

[tyris]

i think that the option i do have is to convert them all to a standard time base, with xslt (no macros possible)
as i'm not able to do it without a minimum help, i made some unsuccesfull search on the net so any code sample would be appreciated. (i can't use javascript i think : the xml is transformed byt the xsl on server side, the extension is set to .xls and send back to the client)
Best regards,
Elise, XML learning girl X-)

 

[MrTom]

what does excel want the output date to look like?

 

[tyris]

well i made some Office HTML tests... it seems that dates in excel are this way :


my base tag is like :

<somedate1>02/27/2002 03:01:20 PM CET</somedate1>
<somedate2>07/17/2001 05:53:47 PM CEDT</somedate2>

opening the file into excel : it doesn't recognize the format (of course)

removing the CET or CEDT sub string and press "enter": excel recognize

saving it to Office HTML : the tags become :

<td class=xl26 align=right width=191 style='width:143pt'
  x:num=&quot;37314.625925925924&quot;>2/27/2002 15:01</td>

<td class=xl26 align=right width=191 style='width:143pt'
  x:num=&quot;37089.745682870373&quot;>7/17/2001 17:53</td>

So i guess there is a number generation (remove x:num="37089.745682870373" and it won't work) and a transformation (but i guess that if i keep 03:01:20 PM in stead of 15:01 it will also work). If you have an easier solution...

i think that the step i must do in my xslt are :
-find tags containing "date" string (possible ? or is therea way to "detect" dates (skeptic)? )
-reformat date (the hardest part ;-) here a begin of sample code would be appreciated )

Best regards,
Elise, XML learning girl X-)

 

[chiph]

MrTom, Tyris -- if you go into control panel and change your Locale info (say, to France) you'll find that Excel changes it's formatting. By using the ISO-8601 format you won't have this problem.

Chip H.