This is the mail archive of the
xsl-list@mulberrytech.com
mailing list .
Re: date calculation from Excel 1900 Format
- To: Jeni Tennison <mail at jenitennison dot com>
- Subject: Re: [xsl] date calculation from Excel 1900 Format
- From: Xiaocun Xu <xiaocunxu at yahoo dot com>
- Date: Tue, 12 Jun 2001 05:58:07 -0700 (PDT)
- Cc: xsl-list at lists dot mulberrytech dot com
- Reply-To: xsl-list at lists dot mulberrytech dot com
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