[XSL-LIST Mailing List Archive Home]
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:37:53 +0100
> 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
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:with-param name="date-time" select="'1900-01-01T00:00:00'" />
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,
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list