using EXSLT {RE: date calculation from Excel 1900 Form

>Date: Tue, 12 Jun 2001 08:40:45 -0700 (PDT)
>From: Xiaocun Xu <xiaocunxu@xxxxxxxxx>
>Subject: Help needed with using EXSLT {RE:  date calculation from Excel 1900 Format}
>To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
>MIME-Version: 1.0
>Content-Type: text/plain; charset=us-ascii
>
>Hi,
>
>  This is my first try with EXSLT.  Following Jeni's
>advice, I am trying to use the dates-and-times
>functions to convert Excel 1900 date format using
>Saxon 6.3.
>  I downloaded date.zip and unzipped it in c:\test
>directory.  When I tried to run Saxon, I get the
>following error:
>Error at xsl:value-of on line 11 of
>file:/C:/test/test1.xsl:
>  The URI http://exslt.org/dates-and-times does not
>identify an external Java class
>Transformation failed
>
>test1.xsl looks like:
><xsl:stylesheet version="1.0"              
>xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>xmlns:date="http://exslt.org/dates-and-times"
>extension-element-prefixes="date">               
><xsl:import
>href="date/functions/add/date.add.function.xsl"/>
><xsl:template match="/">
><xsl:value-of select="date:add('1900-01-01T00:00:00',
>date:duration(37257.041667 * 60 *60 *24))"/>
></xsl:template>
></xsl:stylesheet>
>
>If I just use <xsl:import href="date/date.xsl" />, the
>error I get would be:
>Error at func:script on line 3 of
>file:/C:/test/date/functions/date-time/date.date-time.xsl:
>  Unknown Saxon extension element
>Transformation failed
>
>How should I properly access date:add() and
>date:duration() functions using Saxon?
>
>Much thanks,
>Xiaocun
>
>--- Michael Kay <mhkay@xxxxxxxxxxxx> wrote:
>> 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
>>
>> >
>> > --- Jeni Tennison <mail@xxxxxxxxxxxxxxxx> 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/

-- 
======================================================================
B. Tommie Usdin                        mailto:btusdin@xxxxxxxxxxxxxxxx
Mulberry Technologies, Inc.                http://www.mulberrytech.com   
17 West Jefferson Street                           Phone: 301/315-9631
Suite 207                                    Direct Line: 301/315-9634
Rockville, MD  20850                                 Fax: 301/315-8285
----------------------------------------------------------------------
  Mulberry Technologies: A Consultancy Specializing in SGML and XML               
======================================================================

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