Sharepoint By Dummies

See Also: http://stephenbooth.brandyourself.com/

Possible Work-Around To Date Format Issue (Sharepoint 2010)

with 2 comments

We’d been having a problem with comparing dates on a Sharepoint 2010 site which I documented here on my Blogger blog.   To summarise, we wanted to compare the Due Date of tasks with the current date and display an alerter depending on if the due date was in the future, now or past.  The problem was that we found that some dates were being interpreted as US format and others as UK format (we’re in the UK so all our dates should be UK format).  If a date was legal in both formats (e.g. 12/07/2011) then it would be interpreted as US (so 12/07/2011, 12th July 2011 as far as we’re concerned, would be interpreted as 7th December 2011) but if it was legal only in UK format (e.g. 13/07/2011) it would be interpretred as UK format (i.e. 13th July 2011).  If 12/07/2011 is the due date and today is 13/07/2011 this can give very different answers. 

After researching on the web and asking on various forums (see the blogger post) I still haven’t found a solution.  I do suspect that it’s probably a configuration issue with the underlying servers and have asked the server admins to check it out and am awaiting their response.  In the mean time I have foudn a workaround that seems to get the job done.

To display the alerters one of my colleagues had found some code here (see the answer by ‘swirch’ on Friday, April 9th, 2010 2:10PM) and had implemented it successfully on a different site (Sharepoint 2007 on a different server).  When I came to implment it on our Sharepoint 2010 site it failed utterly, didn’t raise an error but didn’t display the correct alerters.  By looking at the returned values in long date format it was obvious that the dates were being interpreted differently as detailed above.  The interpretation of the dates is done by three lines of XSL:

<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)"/>

$TodayDate is a parameter passed to the XSL Template and these lines are supposed to break it down into the Year, Month and Day elements.  A similar set of lines break down the other date, these elements are then fed in to a formula which calculates Julian Date, the number of days since 12:00 on Monday January 1st,  4713 BC.  By subtracting these from each other the number of days between the two dates is given.

I found another person who reported the same problem and gave some different code to break down the date:

<xsl:variable name="day" select="substring-before($dateValue,’/')"/>
<xsl:variable name="month" select="substring(substring-after($dateValue,’/'),1,2)"/>
<xsl:variable name="year" select="substring(substring-after(substring-after($dateValue,’/'),’/'),1,4)"/>

This treats the date as just a plain text string and worked fine for the storted date (DueDate) but returned an invalid value for the current date.  After some investigation it became apparent that the function to return the current date (TodayISO()) was returning a string in a different format to that the code was expecting.  If the date handling was working then this would be no problem as we could just use FormatDateTime to put it in the correct format.

After manually dissecting the string returned by TodayISO() I eventually came up with the code to divide it correctly.  The full code is now:

##Part 1 - Insert into <xsl:stylesheet> section of web part
<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="Day" select="substring($TodayDate, 9, 2)"/>
<xsl:with-param name="Month" select="substring($TodayDate, 6, 2)"/>
<xsl:with-param name="Year" select="substring($TodayDate, 0, 5)"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="JulianStartDate">
<xsl:call-template name="calculate-julian-day">
<xsl:with-param name="Day" select="substring-before($StartDate,'/')"/>
<xsl:with-param name="Month" select="substring(substring-after($StartDate,'/'),1,2)"/>
<xsl:with-param name="Year" select="substring(substring-after(substring-after($StartDate,'/'),'/'),1,4)"/>
</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>
##Part 2 - Insert into location where graphic is to appear
<td lang="en-gb" class="ms-vb">
<xsl:variable name="DueDateDiff">
<xsl:call-template name="DateDiff">
<xsl:with-param name="StartDate" select="@DueDate" />
<xsl:with-param name="TodayDate" select="ddwrt:TodayIso()" />
</xsl:call-template>
</xsl:variable>
<img alt="Indicator" lang="en-gb">
<xsl:attribute name="src">
<xsl:choose>
<xsl:when test="@Status = Complete">images/blue_ball.png</xsl:when>
<xsl:when test="$DueDateDiff &lt; -1">images/red.png</xsl:when>
<xsl:when test="$DueDateDiff &lt;= 1">images/Anb.png</xsl:when>
<xsl:otherwise>images/green.png</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
</img>
<xsl:value-of disable-output-escaping="yes" select="string($DueDateDiff)" />
</td>

This expects a dueDate in the format 12/07/2011 and a string retuirned from TodayISO() in the format 2011-07-12T16:44:25Z (the format our system was returning it in, this may vary according to the localisation settings of your system.

It’s been a learning experience!

Advertisements

Written by stephenboothuk

July 13, 2011 at 5:21 pm

Posted in Uncategorized

2 Responses

Subscribe to comments with RSS.

  1. Solution / Work around
    I ended up using an out of the box calculated column (A) to subtract PlannedDate from ActualDate. If completed late it is a negative number.
    I then used another calculated column (B) and an If statement to return Yes if calculated column (A) was a negative number.
    I then set the conditional formatting on calculated column (B), such as if Yes then show an image (such as an exclamation mark image).
    Long winded work around but got there.

    I believe it works for DueDate vs Today() in a calculated column as well.

    John

    November 23, 2012 at 8:41 am

    • Unfortunately if you try to use [today] in a calculated column you get an error message that you can’t use dynamic pseudo columns such as [Today] and [Me] in calculated columns. This makes sense as calculated columns are only calculated when the item is saved. If the [PlannedDate] – [Today] calculation was allowed then you’d land up with situations like:

      Presume PlannedDate=01/01/2013 (all dates UK format)

      • creation @ 31/12/2012 = 1
      • no change @ 01/01/2013 = 1
      • no change @ 02/01/2013 = 1
      • no change @ … =1
      • no change @ 14/01/2013 = 1
      • Update @ 15/01/2013 = -14

      The field would only go negative when the item got updated, in this case 2 weeks late.

      stephenboothuk

      November 23, 2012 at 5:53 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Speeches I Would Have Given

What I would have said at conference

markvale.wordpress.com/

Maybe mildy useful to someone!

David Hunt, PE... Mechanical Engineer on the loose!

VALUE = Creativity + Diverse Experience + Critical Thinking

Not Literally Productions

Comedy, parody, tutorials and more!

catastraspie

A personal blog about having Asperger's

Dementia and Elderly Care News

News and research of interest to people involved in Dementia and Elderly Care

Marc D Anderson's Blog

A Knowledge Management Zealot Speaks Out

Codeless Solutions for SharePoint

Bryon Wyly is a SharePoint Developer dedicated to finding the limits to out-of the-box SharePoint solutions

gordonlyew.wordpress.com/

the political blog of gordon lyew

SharePoint Business Analyst

Just another WordPress.com weblog

%d bloggers like this: