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)

From: Michael Blumenthal's BlumenthalIT.NET

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.