Saturday, May 11, 2013

Calculated Columns In SharePoint 2010


Listed here are common functions used in SharePoint 2010 calculated columns.  See the references for additional details.


Conditional Functions    
Functions Format Example {Result}
Return value at number index CHOOSE(Num, Val1, Val2, ...) CHOOSE(3, "A", "B", "C", "D") {"C"}
Simple If - Then IF(Condition, Val1, Val2) IF([Modified] > [Created], "Changed",
"Original") {"Changed" or "Original"}
Date and Time Functions    
Functions Format Example {Result}
Get Year YEAR (Date) YEAR([Created]) {2011}
Get Month MONTH (Date) MONTH([Created]) {3}
Get Week WEEKDAY (Date) WEEKDAY([Created]) {2}
Get Day DAY (Date) DAY([Created]) {29}
Get Hour HOUR (DateTime) HOUR([Created]) {13}
Get Minute MINUTE (DateTime) MINUTE([Created]) {51}
Get Second SECOND (DateTime) SECOND([Created]) {26}
Create Date DATE(Year, Month, Day) DATE(2011, 3, 29) {5/10/2013}
Get Date Difference DATEDIF (Date1, Date2, Unit) DATEDIF("5/5/2013","5/10/2013","M") {5}
Create Date DATEVALUE (Text) DATEVALUE("5/10/2013") {5/10/2013}
Create Time TIME (Hour, Minute, Second) TIME(19,45,21) {1/1/1900 7:45:21 PM}
Get Formatted Time TEXT([DateTimeField],"hh:mm:ss") TIMEVALUE("19:45") {1/1/1900 7:45 PM}
Get Formatted Weekday TEXT([DateField],"dddd")
Get Formatted Month TEXT([DateField],"mmmm")
Get Formatted Year TEXT([DateField],"yyyy")
Get Formatted Date TEXT([DateField],"mmmm dd, yyyy" September 17, 2011
Get AM/PM IF(HOUR([Time]) < 12,"AM","PM") AM
Get Day/Night IF(AND(HOUR([Time])>6,HOUR([Time])<18),"Day","Night") Day
Get Wk commencing [Date]-WEEKDAY([Date])+1 1/2/2011
Get Week number ROUNDDOWN(([Date]-DATE(YEAR([Date]),1,1)+WEEKDAY(DATE(YEAR([Date]),1,1))-
Formatting Function    
Functions Format Example {Result}
Format Number TEXT([Sales],"$#,###.00") $1,234.56
Logical Functions    
Functions Format Example {Result}
AND AND (Condition1, Condition2) AND(4>=3,3>2) {True}
OR OR (Condition1, Condition2) OR(4>=3, 3<2) {True}
NOT NOT (Condition1) NOT(1=1) {False}
Math Functions    
Functions Format Example {Result}
Take Absolute ABS (Number) ABS(-1) {1}, ABS(1) {1}
Round to Even EVEN (Number) EVEN(0.5) {2}, EVEN(-1) {-2}
Round to Odd ODD (Number) ODD(1.5) {3}, ODD(-2) {-3}
Calculate Factorial FACT (Number) FACT(5) {5*4*3*2*1 = 120)
Find remainder MOD (Number1, Number2) MOD(5, 4) {1}, MOD(2010, 20) {10}
Get PI PI () PI() {3.14159265358979}
Round to specific number ROUND (Number1, Number2) ROUND(327.598, 1) {327.6}
Round up ROUNDUP (Num1, Num2) ROUNDUP(52.0001, 0) {53}
Round down ROUNDDOWN (Num1, Num2) ROUNDDOWN(327.598, 1) {327.5}
Get the sign SIGN (Number) SIGN(-5.9786) {-1}, SIGN(20678) {1},
SIGN(0) {0}
Calculate Square Root SQRT (Number) SQRT(64) {8}
Truncate TRUNC (Number) TRUNC(37.999999) {37}
Convert text to number VALUE (Text) VALUE("57") {57}, VALUE("00:05") {00:05}
Get the average AVERAGE (Num1, Num2, ...) AVERAGE(10, 0, "", "0") {5}, AVERAGE(5, 0,
TRUE) {2.5}
Get the average, ignore nonnumbers AVERAGEA (Num1, Num2, ...) AVERAGEA(10, 0, "", "0") {2.5}, AVERAGEA(5,
0, TRUE) {2}
Get the number of numbers COUNT (Num1, Num2, ...) COUNT(10, 0, "", "0") {2}, COUNT(5, 0,
TRUE) {2}
Count all items COUNTA (Value1, Value2, ...) COUNTA(10, 0, "", "0") {4}, COUNTA(5, 0,
TRUE) {3}
Get Max value MAX (Number1, Number2, ...) MAX(0, 2, "76", 100) {100}
Get largest number MAXA (Number1, Number2, ...) MAXA(0, 2, "76", 100, "Telephone", TRUE)
Get Min value MIN (Number1, Number2, ...) MIN(0, 2, "76", 100) {0}
Get smallest number MINA (Number1, Number2, ...) MINA(0, 2, "76", 100, "Telephone", TRUE)
Total all numbers SUM (Number1, Number2, ...) SUM(0, 2, "76", 100, TRUE) {179}
Text Functions    
Functions Format Example {Result}
get length of text LEN (Text) LEN("Hello") {5}
Get N characters from left LEFT (Text, Number) LEFT("I quite like camping", 6) {"I quit"}
Get N characters from right RIGHT (Text, Number) RIGHT("I quite like camping",4) {"ping"}
Get N1 characters starting at N2 MID (Text, Num1, Num2) MID("I quite like camping", 9, 11) {"like
Gets index of search text in string SEARCH (Text1, Text2, Num) SEARCH("Banana", "Banana Banana", 4) {8}
Convert text to lower case LOWER (Text) LOWER("uRGEnT") {"urgent")
Convert text to upper case UPPER (Text) UPPER("uRGEnT") {"URGENT")
Capitalize first character each word PROPER (Text) PROPER("uRGEnT TICket") {"Urgent Ticket")
remove spaces from text TRIM (Text) TRIM(" some text ") {"some text"}
Cleans text of nonprintable characters CLEAN (Text) CLEAN("Line1?? Line2??") {"Line1 Line2"}
Replaces Number2 characters starting at Number1 from Text1 with Text2 REPLACE (T1, N1, N2, T2) REPLACE("Hello",2,4,"i") {"Hi")
Join text strings CONCATENATE (T1, T2, ...) CONCATENATE("A","B","C") {"ABC"}
Converts a number to a currency text, with the given number of decimals DOLLAR (Number, Decimals) DOLLAR(12.567,2) {"$12.57"}
Determine whether two values are identical EXACT (Text1, Text2) EXACT("Hi","hi") {False}
Returns Number with the given number of decimals as text (commas optional) FIXED (Num, Dec, NoCommas) FIXED(1057.26,1,TRUE) {"1057.3"},
FIXED(1057.26,0,FALSE) {"1,057"}
Repeat text N times REPT (Text, Number) REPT("Hi",3) {"HiHiHi"}
Convert text to value TEXT (Value, Format) TEXT([Created], "yyyy|mm") {"2011|03"}
Functions Format Example {Result}
Add, Subtract, Multiply, Divide +, -, *, /  
Power (e.g. [Field]^2 = Squared) ^ 4^3 {64}
Concatanate (put two text values or
fields together)
& "4" & "3" {"43"}, [Field1] & [Field2]
Equal =  
Not Equal To <>  
Greater Than >  
Less Than <  
Greater than or equal to >=  
Less than or equal to <=  
Type / Error Checking Functions    
Functions Format Example {Result}
Determine if blank value ISBLANK (Value) IF(ISBLANK([Assignee]), "Needs assigning",
Determine whether value returns an error
(except #N/A), False otherwise
ISERR (Value) ISERR(#REF!) {True}
Determine whether value returns error ISERROR (Value) ISERROR(#REF!) {True}
Determine whether value is logical ISLOGICAL (Value) ISLOGICAL(FALSE) {True}
Returns True if Value returns error #N/A,
False otherwise
ISNA (Value) ISERR(#N/A) {True}
Returns True if Value is not text or is
empty, False otherwise
Returns True if Value is a Number, False
ISNUMBER (Value) ISNUMBER(2) {True}, ISNUMBER("Two") {False}
Returns True if Value is Text ISTEXT (Value) ISTEXT(2) {False}, ISTEXT("Two") {True}


No comments: