This is the mail archive of the xsl-list@mulberrytech.com mailing list .


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]

RE: date calculation from Excel 1900 Format


Hi, Jason:

  Thanks for the tip, that is pretty interesting
indeed.  Unfortunely, I am converting Excel in Java
environment using Saxon, so I don't think I would able
to take advantage of build-in VB functions such as
CDate().  Anything similar exists can be used within
Saxon/Java?

Thanks,
Xiaocun

--- "Diamond, Jason" <Jason.Diamond@MKG.com> wrote:
> 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@yahoo.com]
> Sent: Monday, June 11, 2001 11:50 AM
> To: xsl-list@lists.mulberrytech.com
> 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
> 


__________________________________________________
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


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]