Re: 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@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/

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