03
Dec
10

Conditional Formatting and date comparisons with SharePoint Designer 2010

So here’s a fun one that we at SharePoint Rx were struggling with the other day. 

We were creating a dashboard using the DVWP in SharePoint Designer 2010 and had to perform some date comparisons to control the display of indicators.  If you’ve used SPD in the past, you know that the goal is to NOT write code, but to use the “coding by clicking” capabilities of SPD and allow it to write the code (in this case XSL) to control your conditional formatting.  Every time I use SPD for something like this, I always seem to run into a new problem, even though I know it should be a simple task. 

“Am I the only one with this issue?” he asks.

Here’s the end goal…to display a status indicator for tasks to indicate whether they were completed on time, completed late, not complete and not late, and not complete and late.  You can see the four icons I chose to indicate these statuses. 

image

My “conditions” are as follows:

clip_image001 Status equals “completed” and Due Date >= Completed Date

clip_image002 Status equals “completed” and Due Date < Completed Date

clip_image003 Status not equals “completed” and Due Date >= [Current Date]

clip_image004 Status not equals “completed” and Due Date < [Current Date]

One of the issues you immediately experience is the comparison of date values.  Using SPD, I did an “un-advanced” comparison in my condition statements and never got the correct results.  SPD wrote some XSL like this (I broke it up so it’s a little easier to read):

<xsl:if test=”normalize-space($thisNode/@Status) = ‘Completed’

and

ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@DueDate))) &gt;=
normalize-space(@Completed_x0020_Date)” ddwrt:cf_explicit=”1″>

<img src=”_layouts/images/kpinormal-0.gif” width=”16″ height=”16″ />

</xsl:if>

So we have a “Due Date” that has been operated on by ddwrt:DateTimeTick (see if you can find any documentation on this function) and is returning the number of days from January 1, 1900 comparing to the “Completed Date” in the form “MM/DD/YYYY.”  That doesn’t work!

Sidebar rant…Come on, Microsoft!  The only documentation on the ddwrt namespace is from a non-MS person (Serge van den Oever ) and is from 2005…for SharePoint 2003?

So, let’s look at the “Advanced” condition criteria that does work.

image

We used the completely undocumented ddwrt:DateTimeTick function and applied it equally to both dates.

image

When performing the comparison with [Current Date], here’s what we did.

image

We were successful with using the $Today variable, but I’ve seen others who have also used ddwrt:Today.

image

Here are the criteria for all four conditions.

@Status = ‘Completed’ and ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@DueDate))) >= ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Completed_x0020_Date)))

@Status = ‘Completed’ and ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@DueDate))) < ddwrt:DateTimeTick(ddwrt:GenDisplayName(string(@Completed_x0020_Date)))

$thisNode/@Status != ‘Completed’ and ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@DueDate))) >= ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))

$thisNode/@Status != ‘Completed’ and ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@DueDate))) < ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today)))

Now, I’d like to believe that features in SPD 2010 that were in SPD 2007 were all functioning correctly, but I seem to have lots of problems using the “All formatting visible” functionality.  I found that each time I changed this I must

  • Save the page
  • Press F5 to refresh (sometimes more than once)

Not sure what that’s about or if it’s just one of my settings, but it sure is annoying.

image

Of course, the idea is that you want to make all your conditionally formatted elements visible so you can easily work on them.

image

FYI, if you want to determine which condition applies to which element, you can select the condition and you should notice a highlighting of the element to which the condition corresponds. 

image

Keywords:

Conditional formatting

SharePoint Designer 2010

Date comparison

Data view web part

ddwrt namespace

About these ads

21 Responses to “Conditional Formatting and date comparisons with SharePoint Designer 2010”


  1. December 24, 2010 at 1:42 am

    Nice post. It helped me a lot because I had a similar problem. Comparing dates was a nightmare for me, because I use Slovene date formating which is ‘dd.MM.yyyy’ and not the US one (‘MM-dd-yyyy’). The problem is that the functions FormatDate and FormatDateTime behave strange. For example I can’t use a dot sign as a delimiter. It only works with the’-‘ sign. In the end I found a workaround and ignored this two functions. My solution for Slovne date format is here http://amavs.wordpress.com/2010/12/16/date-comparison-formatdatetime-function-ddwrt-namespace/.

    • 2 Russell Wright
      December 24, 2010 at 7:27 am

      Very glad it was of some help to you. Great post! Did you experiment any with the DateTimeTick function? I found that to be most useful for date comparison purposes, but not if it doesn’t understand the European date convention.

  2. 3 chandrika
    January 3, 2011 at 5:36 am

    hi,
    i need help in displaying different icons for differernt condition as specified by u.
    but at any time i am getting only one icon displayed for any condition.
    Please can you give me step by step process of displaying different icons for differernt conditions?

    thanks in advance

  3. 4 Rob Creamer
    April 6, 2011 at 9:15 am

    The decompiled class BaseDdwRuntime (in Microsoft.SharePoint.WebPartPages) shows DateTimeTick returning DateTime.Ticks, which is the number of 100ns intervals since Jan 1, 1900. This means that you can refine your tests to more precise scales than days.

  4. 6 Tasha
    June 1, 2011 at 8:56 am

    Man, I wish I would have found this last week! Instead I implemeted a pretty complicated XSL solution to do this when my attempts at using the “OOTB” advanced conditions failed. For future reference, is this methodology useable for date ranges? For example, if an item is due between today and 7 days from today? How would you alter the formatting to add days if it is possible?
    Thank you for your help, and great post.
    ~Tasha

  5. 7 Dave
    June 1, 2011 at 10:15 am

    Hi Russell,

    Thank you for your post. Great help, but would like to ask how one would set a condition where due date is say 7 days away.

    Could you help me with that?

  6. July 8, 2011 at 5:09 am

    I have a similar issue to the questions about dates within 7 days, I blogged about it here: http://sharepointbydummies.blogspot.com/2011/07/more-problems-with-dates-2010-this-time.html

    In summary we have a Due Date field in a list and want to display and alerter depending on if the due date more than a certain number of dates int he future, is closed/recently passed and if it’s passed by more than a certain number of days.

    Not solved it yet but there’s a couple of links in my entry that might be useful. Unfortunately we’re all XSL neophytes here and Sharepoint knowledge is just what we’ve picked up as we’ve gone along (mostly in 2007).

    It’s fairly easy to do these sorts of calculations in Excel so I’m really surprised the functionality doesn’t exist in Sharepoint.

    Any guidance on how we can achieve this would be gratefully received.

    Thanks

    Stephen

  7. 9 troy
    September 18, 2012 at 2:37 am

    Hi,

    I am currently looking for a solution to filter on a dataview to bring back birthdays for current and month and 2 weeks.

    I currently use the following:

    [substring-after(@ows_BirthMonthNumber,’#’)=number(ddwrt:FormatDateTime(string(ddwrt:Today()),1033,’MM’))]

    This brings back for the entire month but i need something that will bring back for 2 weeks in that month.

    Any ideas?

    Regards

    Troy

  8. November 8, 2012 at 1:52 am

    I have tried to add a column of the 4 status indicators to my dvwp using SPD, but it spits the dummy in IE.
    Are there any tricks to adding a column in SPD and the 4 images?

  9. 11 Artem
    November 14, 2012 at 1:50 pm

    Another solution: http://www.sparqube.com/sharepoint-status-indicator
    No code required, condition editor and displayed in list views.
    Minuses: it is not free

  10. April 4, 2013 at 7:28 pm

    I have a calculated column of =Today()-[Due Date] which I use to conditionally format an image. My problem is the calculated column does not update each day. It only updates on item creation or edit. Is there a way to “refresh” the item and therefore make the image conditionally format correctly?

    • 13 Russell Wright
      April 11, 2013 at 12:26 pm

      No, I believe that is a known issue. You might create a workflow that runs each day to update it. Or you could install and use the HarePoint workflow scheduler, which can take care of lots of issues like these. It’s free!


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


Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

December 2010
M T W T F S S
« Nov   Jan »
 12345
6789101112
13141516171819
20212223242526
2728293031  
Support Wikipedia

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: