Tuesday, August 4, 2009

Function reference calculated fields in Sharepoint

Some times you need a list of functions you can use in Sharepoint calculated fields. It seems that this is available in your local machine...just go to: C:\Program Files\Microsoft Office\Office12\1033\STSLIST.CHM (if you got Office/Access 2007 installed)



or if you need an online version: wssdemo

or on your local sharepoint server:

http://server-name/_layouts/help.aspx?lcid=1033&cid0=MS.OSS.manifest&tid=MS.OSS.CH10176029&sq=calculated%20column


Show the year/week in Sharepoint

In order to group per week in a Sharepoint list, I have used the following function:

=IF([Completed date];YEAR([Completed date])&"/"&IF((INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1)>10;INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1;"0"&INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1);"Uncompleted")

Actually the code to determine the weeknumber is:

=INT(([Completed date]-DATE(YEAR([Completed date]);1;1)+(TEXT(WEEKDAY(DATE(YEAR([Completed date]);1;1));"d")))/7)+1

So actually what I did was to first detect if the [Completed date] is valid (not empty), if it is it will show the text uncompleted. The next step was to check whether the week number is 2 digits if not then we will add an additional 0 to it, in order to make sure we can sort it correctly.

Modifying pages without the Edit page option in Sharepoint

Sometimes you want to customize a Sharepoint page, however the Edit Page item in the Site Actions Menu is not available. A good example for this is the Display and Edit page of a list item.

You can still do this by adding the ?ToolPaneView=2 parameter after the page url, eg. DispForm.aspx?ToolPaneView=2