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
