Sharepoint By Dummies

You can export the contents of a Sharepoint view to Excel, this is handy for doing further analysis on the data, and maintain a link to the list so that when you synchronise the worksheet any new items will be brought through.

I have a list which contains a number of dates which I exported to Excel.  The data filled in A2 to J13 (A1 to J1 being the headers).  In columns K et seq I crested a number of formulas and used the fill down functionality to automatically copy the function down as far as row 166, i.e. much further down, my thinking being that as more data was populated from Sharepoint the functions would be there to pick it up.

So at this stage the function in K13 is =IF(C13=”",”",C13), this just redisplays the content of C13 but has different formatting applied,  and the formula in K14 is =IF(C14=”",”",C14), which is blank because C14 is blank (this is the purpose of embedding the thing I want to apply in the IF statement).  When I add an item to the list on Sharepoint and synchronise I expect K14 to display the new value, but it doesn’t.  When I now look in K14 the formula now reads =IF(C15=”",”",C15).  It is as if synchorising the list has inserted a new row, updated the formulas to keep them pointing to the same cell (which is now one row lower) but not moved the formula.  Wierd!

Just had a mail from our systems administrators regarding the localisation settings on the server.  they’re all set to UK and the correct timezone so that’s not the cause of our inconsistent date interpretation problem.

To summarise whilst when entering dates they seem to be interpreted as UK format consistently when we try to use them in calculations they seem to be interpreted as US format unless they are not legal US format dates then they are interpreted as UK format.  So entering or selecting 11/07/2011 (11th July 2011) on the Newform.aspx page seems to work fine but in a calculation it will be interpreted as 7th November 2011 whilst 13/07/2011 will always be interpreted as 13th July 2011.  When we subtract one from the other we expect a 2 day difference but get 117 days difference.

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!

Most of my blogs are over on Blogspot but since I seem to be commenting on a lot of WordPress blogs these days I thought I’d create one myself.

My Blogspot Sharepoint Blog (also called Sharepoint By Dummies) can be found here.  I’ll run them in parralell for a while and see what works out.