Tuesday, March 31, 2009

Data View Customization: Calculating the Number of Days Between Two Dates in XSLT

The objective was to display the number of days between two dates such as Today and last Oct. You can't do this in out of the box SharePoint so you have to go to the XLST to perform data manipulations. Unfortunately, XSLT 1.0 doesnt have any date manipulation so we looked at DDWRT.

My colleague, Michael Bollhoefer and I searched all over Google for terms like Date Manipulation, dateDiff xslt, xsl date comparisons, datedif xslt, date difference xslt, etc. We came across a template but for some reason it wasn't returning the correct value. Eventually we came to the conclusion that the date format had to be the root of our error. We needed to convert from a gregorian dateTime format (CCYY-MM-DDT00:00:00Z) into a Juliantime format. Then you can easily subtract or add to the julian numbers to get your desired result. Subtracting the gregorian results with a sub-string function didnt provide accurate results because you have to account for the number of days in each month.


Our research finally led us to Toni Frankola's post on endusersharepoint.com which resulted in the new XSLT:



<xsl:template name="DateDiff">
<xsl:param name="StartDate"></xsl:param>
<xsl:param name="TodayDate"></xsl:param>

<xsl:variable name="JulianToday">
<xsl:call-template name="calculate-julian-day">
<xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),0,5)"/>

<xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),5,2)"/>

<xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($TodayDate), 1033, 'yyyyMMdd'),7,2)"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="JulianStartDate">
<xsl:call-template name="calculate-julian-day">
<xsl:with-param name="Year" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),0,5)"/>

<xsl:with-param name="Month" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),5,2)"/>

<xsl:with-param name="Day" select="substring(ddwrt:FormatDateTime(string($StartDate), 1033, 'yyyyMMdd'),7,2)"/>
</xsl:call-template>
</xsl:variable>

<xsl:value-of select="$JulianStartDate - $JulianToday"></xsl:value-of>
</xsl:template>

<xsl:template name="calculate-julian-day">
<xsl:param name="Year"/>
<xsl:param name="Month"/>
<xsl:param name="Day"/>

<xsl:variable name="JulianDay" select="floor((14 - $Month) div 12)"/>
<xsl:variable name="JulianYear" select="$Year + 4800 - $JulianDay"/>
<xsl:variable name="JulianMonth" select="$Month + 12 * $JulianDay - 3"/>

<xsl:value-of select="$Day + floor((153 * $JulianMonth + 2) div 5) + $JulianYear * 365 + floor($JulianYear div 4) - floor($JulianYear div 100) + floor($JulianYear div 400) - 32045"/>
</xsl:template>


This is the template that DID NOT WORK
http://www.exslt.org/date/functions/difference/index.html

2 comments:

  1. This is great stuff... thanks for sharing; now to adapt it to only return "working" days...

    As a first step, exclude Saturday and Sundays, then for the finishing touch: exclude localized holidays.

    ReplyDelete
  2. Haha... Definitely more work to be done for that. Thanks for being the first comment on the new site Robin!

    ReplyDelete

Note: Only a member of this blog may post a comment.