Convert SharePoint date to serial using XSL

Another day, another SharePoint limitation.

Since you can't use Lookup fields in calculations, if we are trying to compare a Date being used as a Lookup key for a view then we have to resort to XSL. The following template will convert a SharePoint date to a serial number (UPDATE: oops -- made a beginner XSLT mistake and was assigning to variables, not parameters. The revised code is below. UPDATE 2: For some reason, certain select statements using the floor() function made value-of statement static. A better nested version below fixes this bug too.)

<xsl:template name="dateToSerial">
  <xsl:param name="date" />
  <xsl:comment>Returns a serialised version of a SharePoint date.  
	 XSLT algorithm taken from
	 Value adjusted to match MS offset of January 1, 1900.
	 Note that this does not implement the Excel 1900 date bug so dates in 1900 may not be equal.
  <xsl:apply-templates select="." mode="dateToSerialCalc">
	<xsl:with-param name="year" select="ddwrt:FormatDateTime(string($date),1033,'yyyy')" />
	<xsl:with-param name="month" select="ddwrt:FormatDateTime(string($date),1033,'MM')" />
	<xsl:with-param name="day" select="ddwrt:FormatDateTime(string($date),1033,'dd')" />
<xsl:template match="node()" mode="dateToSerialCalc">
  <xsl:param name="year" />
  <xsl:param name="month" />
  <xsl:param name="day" />
  <xsl:apply-templates select="." mode="dateToSerialCalc_S2">
	<xsl:with-param name="y" select="$year + 4800 - floor((14 - $month) div 12)"/>
	<xsl:with-param name="m" select="$month + 12 * floor((14 - $month) div 12) - 3"/>
	<xsl:with-param name="d" select="$day"/>
<xsl:template match="node()" mode="dateToSerialCalc_S2">
  <xsl:param name="y"/>
  <xsl:param name="m"/>    
  <xsl:param name="d"/>    
  <xsl:value-of select="$d + floor((153 * $m + 2) div 5) 
      + $y * 365 + floor($y div 4) - floor($y div 100) 
      + floor($y div 400) - 2415019 - 32075 + 30"/>

The calling string then looks like this:

   <xsl:variable name="RealReportDate" 
    select="msxsl:node-set(substring-after(@Report_x0020_Date.,';#'))" />
   <xsl:apply-templates select="$RealReportDate" 
    mode="dateToSerial" />