Conditional Formatting in SharePoint Designer 2010 doesn’t compare dates correctly

I wrote previously about not being able to pass dates in British format from Time Intelligence Connection Formula filter to Reporting Services report, because although SharePoint displays the dates in British format, but on the server-side it doesn’t deal with them in the same format.  Here is the link to the post.

The same occurred to me when I was trying to apply very simple conditional formatting to add traffic lights for overdue items as below

* Current date = 22/07/2011

As it can be noticed in the above picture that the condition is false for 29/07/2011 but true for 05/08/2011. If I go to advance view the condition it generates behind the scenes is the one below

I forced the Due Date to the correct format in this condition I used “ddwrt:FormatDate” function

ddwrt:DateTimeTick(ddwrt:GenDisplayName(ddwrt:FormatDate(
string($thisNode/@DueDate), 2057, 1))) <
ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))  

In the above line, 2057 is the LCID for British English, and “1” is a simple date format.

And it worked for me

 
Advertisements
  1. #1 by TimC on October 1, 2013 - 8:29 am

    Thanks, this worked for me too. I was trying to find a way to do date calculation with this too, but I couldn’t get that working so I just created a calculated Due Date column and used that with your code above. Thanks.

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

%d bloggers like this: