SharePoint List Data Calculations

SharePoint offers column calculations. These can be complex or simple.

Microsoft has an explanation on Data Calculations here*.*
Microsoft also has a list of Formulas and Functions here*.*
Microsoft also has a list of how to use a calculated list value here*.*
Here is an EXTENSIVE list of functions that can be used

Here is an example of a formula that WILL NOT work:
=IF(Column1<=Column2, "OK", "Not OK")

Here is an example of a formula that WILL work:
=IF(Column1<=Column2*;* "OK"; "Not OK") (Note the semicolons instead of the commas!)

Testing Multiple-Choice Value

In this example, I wanted to test a column that was of type multiple-choice. If it was one of two specific values, I wanted this field to be equal to 1. If it was another choices, I wanted it to be equal to 0:
=IF(OR([Column]="Choice 1", [Column] ="Choice 2"), 1, 0)

In this example, I wanted to test options for a column, setting the calculated value based on options:
=IF([VARIABLE]="Open","Open",IF([VARIABLE]="Unresolved","Substantially Complete", IF([VARIABLE]="Resolved and Closed","Substantially Complete","Work in Progress")))
This basically says, take the Variable drop-down I am working with and,

• If it says "Open", set this field to "Open"
• If it says "Unresolved", set this field to "Substantially Complete"
• If it says "Resolved and Closed", set this field to Substantially Complete
• If it has any other value, set it to "Work in Progress"

Calculated Field for Dates

I had a need to group a list by dates. I came up with 3 options... here's what I did. Oh, and you needf to replace "VARIABLE" with the actual date field you want to calculate off of:

Show the week in the year - “114”

=INT((VARIABLE-DATE(2007,1,1))/7)

Show the year and month - “2009 – 03 (March 2009)”

=TEXT(VARIABLE,"yyyy - ")&TEXT(VARIABLE,"mm")&TEXT(Date," (mmmm")&TEXT(VARIABLE," yyyy)")

Show the year, month and week in that month - “2009 – 03 (March wk 01)”

=TEXT(VARIABLE,"yyyy - ")&TEXT(VARIABLE,"mm")&TEXT(VARIABLE," (mmmm wk ")& IF(TEXT(VARIABLE,"dd")<"08","1)",IF(TEXT(VARIABLE,"dd")<"15","2)", IF(TEXT(VARIABLE,"dd")<"22","3)",”4)”)))

Calculated Field for Text

Suppose you have a column of text called "My Column" and you wanted to pull the first letter of the value for that field. You can do that by creating a calculated column with this as the calculation:

=Left(My Column,1)