Re: [xsl] date calculation from Excel 1900 Format

Subject: Re: [xsl] date calculation from Excel 1900 Format
From: Jeni Tennison <mail@xxxxxxxxxxxxxxxx>
Date: Tue, 12 Jun 2001 10:32:07 +0100
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 ( 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: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" />

[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()

  translate($date-time, 'T', ' ')

to get the format that you're after.

I hope that helps,


Jeni Tennison

 XSL-List info and archive:

Current Thread