SharePoint - Problem comparing dates in Sharepoint 2010, US v UK format

Asked By Stephen Booth on 08-Jul-11 09:49 AM
We have a list which contains a DueDate field, all dates are UK format.  It seems that where the date is valid in US format it is being interpreted as a US format date but when it is invalid in a US formation (days value > 12) it is being interpreted as a UK format).

We are trying to create a dashboard that will display alerters (small graphics) when the due date is close or has passed.

Basically we're looking for something like:

DueDate > [CurrentDate] + 5 days = Green
DueDate <= [CurrentDate] + 5 days and DueDate >= [CurrentDate] - 5 = Amber
DueDate < [CurrentDate] - 5 = Red

We found an XSL template that should do what we need on social.msdn.microsoft.com and it kind of works.  The problem is that depending on the days value of the date it switches between interpreting the date as US or UK format. We have confirmed this by including a line to display the value of $DueDateDiff:

<xsl:value-of disable-output-escaping="no" select="$DueDateDiff" />


So:

24/06/2011 is interpreted, correctly, as 24th June 2011 so when doing the compare on 08/07/2011 gives a difference of 14 days, but, 01/07/2011 is interpreted, incorrectly, as 7th January 2011 so the comparison on 08/07/2011 gives a difference of 182 days.

We thought, based on research on the web, that changing the second parameter of FormatDateTime in lines like:

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


from 1033 (US format) to 2057 (en-gb format) would fix it but this had no visible effect.

We then tried putting two calculated columns in the list called GoAmberOn and GoRedOn that were just calculated from DueDate for the dates that we would expect the alerters to change from Green to Amber and then Amber to Red.  We then used conditional formatting to show the alerters at the appropriate dates:

Green when [CurrentDate] < GoAmberOn
Amber when [CurrentDate] >= GoAmberOn and [CurrentDate] < GoRedOn
Red when [CurrentDate] >= GoRedOn

Although looking at the values of GoAmberOn and GoRedOn it is clear that they are being calculated correctly looking at which alerter(s) are shown it appears that a similar interpretation issue is at work,

Any suggestions as to how this could be fixed.  We're basically learning as we go along so our XSL skills are currently minimal (although I've ordered a couple of books from Amazon on Sharepoint 2010 development which should arrive on Monday so I'm looking to improve in that area).

Thanks

Stephen

pete rainbow replied to Stephen Booth on 10-Jul-11 10:39 PM
if it's expecting us format, instead of changing things to uk format as you have tried

why not try making sure it's all in US format? would that not always work as you are just after offsets and so could hide the actual dates...
pete rainbow replied to Stephen Booth on 10-Jul-11 10:48 PM
btw i'm assuming you have set the language property of the report?

On the Report you can set the Language Property which is in the Localization section.
You will place the language code like en-GB in this property.

 you will see this towards the very bottom:

 <Language>en-GB</Language>
Stephen Booth replied to pete rainbow on 11-Jul-11 04:53 AM
Pete,

as the Due Date needs to be displayed on the page as well as used in this calculation (and used elsewhere) I don't believe we can use US format. 

After posting my original message here I discovered a blog entry by someone in Australia having the same problem: http://sharepointroot.com/2011/06/08/xsltlistviewwebpart-date-format-using-ddwrt/.  I have also been informed by a collerague that this code is working OK on our Sharepoint 2007 site.  I'm wondering if it's a server configuration issue?

If Sharepoint consistently interpreted dates one way or the other (always US or always UK) then I could understand that, it's the switching back and forth that's confusing me.  I'll check the localisation setting as you indicated in your other reply.  As I mentioned before, we're still learning.

Thanks

Stephen
pete rainbow replied to Stephen Booth on 11-Jul-11 07:29 AM
did you try setting the report language to en-gb as i suggested above?
Stephen Booth replied to pete rainbow on 11-Jul-11 08:27 AM
Pete,

yes tried it (on a simplified page, just testing the bare bones so there's less code to wade through to make each change).  No change to results.

Stephen