Blog Archives

SPD Conditional Formatting and Date comparison

Posted by AD

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