SPD Conditional Formatting and Date comparison


My basic challange was how to conditionally format rows on the SharePoint lists, that fulfil selected criteria. I have a column DueDate. If the task is delayed, I’d  like to render it on the orange background.

Date format in my country (Slovenia) is different from the US format. By the way, Slovene date formatting is very similar to the German one and date formatting of other countries in Europe. So definitelly, they have the same problem.

Let’s go back to the challange. I concluded that the basic reason, why conditional formating doesn’t work, is different date formating. It compares US Format date with the date in Slovene  format.

Besides that I found the basic idea, how to “build” the date comparing function. Idea is to convert both dates to numbers and thaen compare this two numbers. Numbers are bulid according to the following patern: yyyyMMdd. This pattern guarantees that dates are comparable. The post where I got the idea is here:

[number(translate(substring-before(@EventDate,'T'),'-','')) >= number(translate(substring-before($Today,'T'),'-','')) and number(translate(substring-before(@EventDate,'T'),'-','')) <= number(translate(substring-before($Today,'T'),'-','')) + 7]

What I can do now? I went through the available Date and String functions in the “Advanced Condition dialog box” and decided to re-format Slovene date to the US one.

Here are the functions, which I can use.

Function Today, TodayIso, FormatDateTime and Format Date are described here.

You can find LCID (Locale Identifier) values on this site

(LCID for Slovenia = 1060, LCID for USA = 1033)

Here is complete description of functions in ddwrt namespace (available in SPD advanced condition editor).

The biggest problem is how to re-format date. I found that the functions FormatDate and FormatDateTime work very specifically and are bad documented.

Here are some examples of the use of these functions.

Examples of FormatDate function (examples in read use TodayIso function; example in blue uses different LCID):

ddwrt:FormatDate(ddwrt:Today(),1060,1)     16.12.2010

ddwrt:FormatDate(ddwrt:Today(),1060,3)     16. december 2010

ddwrt:FormatDate(ddwrt:Today(),1033,3)     Thursday, December 16, 2010

ddwrt:FormatDate(ddwrt:Today(),1060,4)     0:00

ddwrt:FormatDate(ddwrt:TodayIso(),1060,4)     11:02

ddwrt:FormatDate(ddwrt:Today(),1060,5)     16.12.2010 0:00

ddwrt:FormatDate(ddwrt:Today(),1060,7)     16. december 2010 0:00

ddwrt:FormatDate(ddwrt:Today(),1060,12)     0:00:00

ddwrt:FormatDate(ddwrt:TodayIso(),1060,12)     11:01:15

ddwrt:FormatDate(ddwrt:Today(),1060,13)     16.12.2010 0:00:00

ddwrt:FormatDate(ddwrt:Today(),1060,15)     16. december 2010 0:00:00

ddwrt:FormatDate(ddwrt:TodayIso(),1060,15)     16. december 2010 10:58:41

Then I tried function FormatDate with some string. The function doesn’t like a dot as a delimiter in the date format:

ddwrt:FormatDate(’3.1.2010′,1060,15)      *** nothing displayed ***

By default the function recognizes the date as US version (MM-dd-yyyy). If the date is not logical e.g. ’13-1-2010′, then the function recognizes it as an european date ‘dd-MM-yyyy’. Very interesting, but nowhere documented. How can we use this function in Europe? The function is useless for us. Perhaps somebody knows something more about this.

ddwrt:FormatDate(’3-1-2010′,1060,15)       1. marec 2010 0:00:00

ddwrt:FormatDate(’13-1-2010′,1060,15)       13. januar 2010 0:00:00

ddwrt:FormatDate(’1-13-2010′,1060,15)       13. januar 2010 0:00:00

Function FormatDateTime has different parameters:

FormatDateTime(string szDate, long lcid, string szFormat);

Where:
string szDate - either an existing date column or a function (e.g. ddwrt:(Today())
long lcid - a number corresponding to the locale id (e.g. British English is 1033)
string szFormat - e.g. ‘yyyyMMdd’, ‘dd/MM/yyyy’ etc.

Here are some examples on FormatDateTime function:

ddwrt:FormatDateTime(’1-13-2010′,1060,’yyyy’)           2010

ddwrt:FormatDateTime(’1-13-2010′,1060,’yyyy_MM_dd’)        2010_01_13

ddwrt:FormatDateTime(’1-13-2010′,1060,’yyyy-MMM-dd’)      2010-jan-13

I concluded that I can use function FormatDate only with Today and TodayIso functions, but I can’t convert dates in Slovene format. What I had to do is to convert both dates to numbers according to the ‘yyyyMMdd’ patern.

Here is my final solution.

number(concat(substring(string(@DueDate),7,4),substring(string(@DueDate),4,2),substring-before(string(@DueDate),’.’))) < translate(substring-before(ddwrt:TodayIso(),’T’),’-’,”)

I used orange color for delayed tasks and yellow for the task that has to be completed today.

<%@ Register Tagprefix=”ApplicationPages” Namespace=”Microsoft.SharePoint.ApplicationPages.WebControls” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Register Tagprefix=”WebPartPages” Namespace=”Microsoft.SharePoint.WebPartPages” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Register Tagprefix=”Utilities” Namespace=”Microsoft.SharePoint.Utilities” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> <%@ Register Tagprefix=”SharePoint” Namespace=”Microsoft.SharePoint.WebControls” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %> 16.12.2010 0:00
Enhanced by Zemanta
About these ads

About Andrej Dobrovoljc

Andrej Dobrovoljc

Posted on 16. December, 2010, in SharePoint and tagged , , , , , , . Bookmark the permalink. 20 Comments.

  1. Hello Andrej,

    Could you help me with how I would compare dates in the US.

    I want to set a condition where due date is 7 days from now.

    Thank you for your post and help.

    • Convert both dates into numbers (see below how)and then compare this two numbers. Numbers are bulid according to the following patern: yyyyMMdd. In your case the difference between this dates has to be 7 or more.

      Why? Because the US date format is something like that ’2011-06-01′ and when you remove dash signs from the string you get comparable numbers. Difference between such converted dates gives you the exact number of days between them.

      Convert the date into number by using the following function:
      number(translate(substring-before(@EventDate,’T’),’-’,”))

      Function “substring-before” will return just the date part otf the string (without time).
      Function “translate” removes minus signs from the string.
      And the function “number” tranlsates the string into number.

  2. Thank you thank you. It works!!!

  3. Grrrreat!!!

    BIG thanks from Norway :D

  4. Hi Andrej,

    I too am having trouble with comparing dates in a DVWP. I want to conditionally format a row so that it appears as green when the “ExpiryDate” is greater than “Today + 30days”. I’m using the following formula for the conditional formatting:

    number(translate(substring-before(@ExpiryDate,’T’),’-’,”)) > (number(translate(substring-before(ddwrt:TodayIso(),’T’),’-’,”))+30)

    Something is wrong tho because I’ve got documents that fulfil the conditions but the formatting isn’t applied. I’d appreciate any advice,

    regards,

    KP

  5. Hi Andrej,

    I have searched for this solution a month and I finally found something that works! I tested so many formulas and stuff. I was already going to give up, but then I found your post. THANKS a lot, man! I wish I would’ve found this earlier.

    Well you did a good job and the explanation is written very well.

    Regards,
    Peter

  6. I was struggling to implement date comparison with European dates in a data view filter. Your solution did the trick. Huge thanks!

    TAO

  7. Good way of telling, and pleasant article to get data on the topic of my presentation focus, which i am going to present in university.

  8. Hi, Neat post. There’s an issue together with your website in internet explorer, might check this? IE still is the market chief and a large component of people will pass over your great writing due to this problem.

  9. Gert Cleuren

    Hello Andrej

    This is a very nice article, also very close to my own issue which I have on sharepoint designer conditional formatting.

    I would like to do conditional formatting on row level when the value year (this column is a dropdown with predefined years) IS NOT today.Year -1 (The today date can be used by the formula in SPD).

    Is there any way to check what would be the outcome of the formula’s I am using in my conditional formatting? I don’t have any clue against what im doing my comparison, not knowing if I’m heading towards the solution or not…

    Cheers
    Gert

    • Gert Cleuren

      After some trial and erroring, I found the answer to the request: the following formula does conditional formatting on each row where the ‘JAAR’ field is equal to today.year – 1 (previous year).

      But I still have not yet figured out how I can see the output of the formulas for debug purposes, which might come to use sometime.

      Gert

      • Gert Cleuren

        I’m sorry for the double post, but I forgot my formula for other people who might be interested;

        $thisNode/@Jaar = number(ddwrt:FormatDateTime(ddwrt:Today(),2067,’yyyy’) -1)

        Explication:
        $thisNode/@Jaar => Jaar is the name of the column
        ddwrt:Today() => built in function in Sharepoint Designer which returns the date from today.
        2067 => Locale ID which is relative to Belgium
        ‘yyyy’ => format the output to return the year

  10. Oh my goodness! Amazing article dude! Thank you so much, However I am having problems with
    your RSS. I don’t understand the reason why I can’t join
    it. Is there anyone else having identical RSS problems? Anyone who knows
    the solution will you kindly respond? Thanx!!

  11. Unquestionably imagine that that you said.
    Your favourite justification appeared to be on the net the
    simplest thing to consider of. I say to you, I certainly get annoyed at the same
    time as folks think about worries that they plainly do
    not recognize about. You controlled to hit the nail upon
    the highest and also defined out the whole thing without
    having side effect , other people could take a signal.
    Will probably be again to get more. Thank you

  12. There is definately a lot to find out about this topic.
    I love all the points you have made.

  13. Hi! Someone in my Facebook group shared this website with us so I came to check it out.
    I’m definitely enjoying the information. I’m bookmarking and
    will be tweeting this to my followers! Exceptional blog and outstanding design.

  14. Hi there, just became alert to your blog through Google, and found that it’s really informative. I am going to watch out for brussels. I’ll be grateful if you continue this in
    future. A lot of people will be benefited from your writing.
    Cheers!

  15. Your style is so unique compared to other folks I have read stuff from.
    Thank you for posting when you have the opportunity, Guess I will just book mark this blog.

  16. Great blog here! Also your web site loads up very fast!
    What web host are you using? Can I get your affiliate link to your host?
    I wish my site loaded up as quickly as yours lol

  1. Pingback: Sharepoint XSL / XSLT | Just tinkering Blog

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

Follow

Get every new post delivered to your Inbox.

Join 33 other followers

%d bloggers like this: