[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message] 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) 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
|
PURCHASE STYLUS STUDIO ONLINE TODAY!Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced! Download The World's Best XML IDE!Accelerate XML development with our award-winning XML IDE - Download a free trial today! Subscribe in XML format
|