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


Saxon 6.3 doesn't have direct support for the exslt date module, but I
believe some of the functions have been implemented using exslt:function,
which Saxon does support; it just means you have to import these function
definitions.

Mike Kay

> -----Original Message-----
> From: owner-xsl-list@lists.mulberrytech.com
> [mailto:owner-xsl-list@lists.mulberrytech.com]On Behalf Of Xiaocun Xu
> Sent: 12 June 2001 13:58
> To: Jeni Tennison
> Cc: xsl-list@lists.mulberrytech.com
> Subject: Re: [xsl] date calculation from Excel 1900 Format
>
>
> Hi, Jeni:
>
>   Much thanks for the direction, this is definitely
> the way I want to go.  I currently use Saxon 6.0.2,
> just need to upgrade to Saxon 6.3 to access EXSLT.
>   I just checked Saxon website and did not found date
> is supported, they support the following:
> Saxon now supports the EXSLT modules Common, Math,
> Sets, an Functions. The full list of extension
> functions is:
> exslt:node-set()
> exslt:object-type()
> math:min()
> math:max()
> math:highest()
> math:lowest()
> set:difference()
> set:intersection()
> set:distinct()
> set:leading()
> set:trailing()
> set:has-same-node()
> plus the following new elements:
> func:function
> func:result
>   Was date supported but has yet to be updated in the
> release notes?  Either way, I will download Saxon 6.3
> and start trying it out.
>
> Much thanks,
> Xiaocun
>
> --- Jeni Tennison <mail@jenitennison.com> wrote:
> > Hi Xiaocun,
> >
> > > 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?
> >
> > The EXSLT - Dates and Times module
> > (http://www.exslt.org/date - look
> > at the implementer pages for the full set of
> > functions) defines
> > several functions that help with this kind of thing.
> > Most of them
> > (including the ones you need) are implemented as
> > pure XSLT 1.0
> > templates and as functions through EXSLT - Functions
> > (which is
> > supported in Saxon 6.3 and 4XSLT); all are
> > implemented in Javascript,
> > thanks to Chris Bayes.
> >
> > There's no specific conversion function for Excel
> > 1900 format but you
> > can get the dates that you're after by converting
> > the number
> > to a number of seconds, thence to a duration, and
> > then add that
> > duration to 1900-01-01.  With functions:
> >
> >   date:add('1900-01-01T00:00:00',
> >            date:duration(37257.041667 * 60 * 60 *
> > 24))
> >
> > Or with templates:
> >
> > <xsl:call-template name="date:add">
> >    <xsl:with-param name="date-time"
> > select="'1900-01-01T00:00:00'" />
> >    <xsl:with-param name="duration">
> >       <xsl:call-template name="date:duration">
> >          <xsl:with-param name="seconds"
> >                          select="37257.041667 * 60 *
> > 60 * 24" />
> >       </xsl:call-template>
> >    </xsl:with-param>
> > </xsl:call-template>
> >
> > [Trying it out, you should actually use the "1900
> > format" date *minus
> > 2* in your calculation, because the day count starts
> > from 1 rather
> > than 0, and because Excel thinks that 1900 was a
> > leap year (which it
> > wasn't, I think, because it's divisible by 100).]
> >
> > This results in an ISO 8601 date:
> > '2002-01-01T01:00:00'.  You're
> > probably best converting the 'T' to a space using
> > the translate()
> > function:
> >
> >   translate($date-time, 'T', ' ')
> >
> > to get the format that you're after.
> >
> > I hope that helps,
> >
> > Jeni
> >
> > ---
> > Jeni Tennison
> > http://www.jenitennison.com/
> >
> >
>
>
> __________________________________________________
> 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


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