Friday 8 July 2011

More problems with dates, 2010 this time

Run into yet another problem with dates, but on Sharepoint 2010 this time.

Basically it's the same old thing of wanting to display different alerters based on the relative positions of CurrentDate and a date field in an entry.  A straight comparison of is current date less than, greater than or equal to a date field is no problem, easily done in a filter or conditional formatting filter.  The problem comes when we want to find out if CurrentDate is X number of days before or after the date field.

For example a Task list has a Due Date field on each entry.  If CurrentDate is at least 5 days before DueDate we want a green flag, if it's less than 5 days before or after DueDate we want an Amber flag and it it's 5 or more days after Due date we want a red flag.  So if DueDate (dd/MM/yyyy) is 15/07/2011 we want:

Up to 10/07/2011 = Green
11/07/2011 to 19/07/2011 = Amber
20/07/2011 or later = Red

We found a template that looks like it should do this on an MSDN blog site but that doesn't seem to work properly.  From what we can tell it seems that if either DueDate or CurrentDate can be interpreted as a legal US format (MM/dd/yyyy) date (e.g. 08/07/2011, 8th July 2011 but interpreted as 7th August 2011) then that is how it will be interpreted, if it cannot (e.g. 30/06/2011) it will use UK/International format.

From that page there is a link to a different way of getting the difference between two dates which is for Sharepoint 2007.  I've posted a comment asking if that will work in 2010 as well.  We shall see!

I've also been asked to look at if it's possible to just subtract two dates from each other in 2010.  Worth a look maybe (you can't in 2007, so far as we know).

Edited to add (12:10 08/07/2011) Found another blog entry on a related issue that discusses the issues with using dates in conditional formatting, although that seems to be just comparing CurrentDate with a field.  A couple of the comments do ask about comparing with dates +/- CurrentDate.

Edited to add (14:51 08/07/2011) Now asked a question about this on EggheadCafe.  It looks like the same issue of US vs UK date format affects just doing straight comparison's of dates in Conditional formatting.  We didn't have this problem on the Sharepoint 2007 server so presumably it's either a difference betweenthe two servers or between Sharepoint 2007 and 2010.

Edited to add (15:58 08/07/2011) Found a useful site the LCID (locale identifiers) in Sharepoint. Bascially the number you use, say, in the second parameter of FormatDateTime tells it which locale format to use.  So, 1033 is US so it will format dates as MM/dd/yyy but 2057 is UK so dates will be formatted as dd/MM/yyyy.  Doesn't solve the problem but could be useful to know in the future.

Edited to add (16:29 08/07/2011) Looks like I'm not the only one with this problem, found someone's blog entry about the inconstent interpretation of dates.  Unfortunately his solution won't solve my problem (different problem just impacted by same underlying issue).

Edited to add (13:57 12/07/2011): Posted a question about this on Go 4 Sharepoint : http://www.go4sharepoint.com/Forum/inconsistent-interpretation-dates-us-vs-23644.aspx

We're having the Locale setting on the servers checked as that's the only thing left really. 

2 comments:

  1. Solution / Work around
    I ended up using an out of the box calculated column (A) to subtract PlannedDate from ActualDate.

    Calculated columns seem to ignore the US date issue.

    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.

    ReplyDelete