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


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/



 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]