This is the mail archive of the
xsl-list@mulberrytech.com
mailing list .
RE: date calculation from Excel 1900 Format
- To: xsl-list at lists dot mulberrytech dot com
- Subject: RE: [xsl] date calculation from Excel 1900 Format
- From: Xiaocun Xu <xiaocunxu at yahoo dot com>
- Date: Mon, 11 Jun 2001 19:46:52 -0700 (PDT)
- Reply-To: xsl-list at lists dot mulberrytech dot com
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