RE: [xsl] date calculation from Excel 1900 Format

["Diamond, Jason" <Jason.Diamond@xxxxxxx>]

Since you're converting from Excel, I'm assuming you're using Windows and
probably also MSXML. If you don't mind using extension functions the
following transform almost gets you what you want:

<xsl:transform
    version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:e2w="urn:foo:excel-to-w3c-date-time-format"
>
  
  <xsl:output method="text" encoding="UTF-8"/>
  
  <msxsl:script 
      language="VBScript" 
      implements-prefix="e2w">

    <![CDATA[
    
    Function convert(excelDate)
      dt = CDate(excelDate)
      convert = _
        Year(dt) & "-" & _
        Month(dt) & "-" & _
        Day(dt) & " " & _
        Hour(dt) & ":" & _
        Minute(dt) & ":" & _
        Second(dt)
    End Function
    
    ]]>
    
  </msxsl:script>
  
  <xsl:template match="/">
    <xsl:value-of select="e2w:convert(37257.041667)"/>
  </xsl:template>
  
</xsl:transform>

The output looks like this:

2002-1-1 1:0:0

so you'd have to left pad the digits to really get it into the proper
format.

Hope this helps,
Jason.

-----Original Message-----
From: Xiaocun Xu [mailto:xiaocunxu@xxxxxxxxx]
Sent: Monday, June 11, 2001 11:50 AM
To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
Subject: [xsl] date calculation from Excel 1900 Format


Hi,

  During my conversion from Excel to XML, I needed to
convert dates.  Excel stores date in so called "1900
format", which is the number of days since 1/1/1900 in
decimal format, e.g. 37257.041667 for 1/1/2002 1AM.  I
need to convert this back to YYYY-MM-DD HH:MM:SS
format when I convert the Excel into XML.
  Has anyone did date conversion calculation, esp.
Excel 1900 format, with XSL?

Much thanks,
Xiaocun

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/

 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list

 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list