Subject: Re: [xsl] date calculation from Excel 1900 Format From: Xiaocun Xu <xiaocunxu@xxxxxxxxx> Date: Tue, 12 Jun 2001 05:58:07 -0700 (PDT) |
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
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
Re: [xsl] date calculation from Exc, Jeni Tennison | Thread | RE: [xsl] date calculation from Exc, Michael Kay |
RE: [xsl] Configuring Saxon as the , Pywell, Andrew | Date | RE: [xsl] date calculation from Exc, Michael Kay |
Month |