Subject: RE: [xsl] date calculation from Excel 1900 Format From: "Michael Kay" <mhkay@xxxxxxxxxxxx> Date: Tue, 12 Jun 2001 14:17:47 +0100 |
Saxon 6.3 doesn't have direct support for the exslt date module, but I believe some of the functions have been implemented using exslt:function, which Saxon does support; it just means you have to import these function definitions. Mike Kay > -----Original Message----- > From: owner-xsl-list@xxxxxxxxxxxxxxxxxxxxxx > [mailto:owner-xsl-list@xxxxxxxxxxxxxxxxxxxxxx]On Behalf Of Xiaocun Xu > Sent: 12 June 2001 13:58 > To: Jeni Tennison > Cc: xsl-list@xxxxxxxxxxxxxxxxxxxxxx > Subject: Re: [xsl] date calculation from Excel 1900 Format > > > 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@xxxxxxxxxxxxxxxx> 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 > XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
Re: [xsl] date calculation from Exc, Xiaocun Xu | Thread | Re: [xsl] date calculation from Exc, Jeni Tennison |
Re: [xsl] date calculation from Exc, Xiaocun Xu | Date | Re: [xsl] HTML in CDATA, Carsten Heinrigs |
Month |