Introduction
Listed here are common functions used in SharePoint 2010 calculated columns. See the references for additional details.
Definitions
References
Listed here are common functions used in SharePoint 2010 calculated columns. See the references for additional details.
Definitions
| 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") TEXT([DateField],"ddd") |
Tuesday Tue |
| Get Formatted Month | TEXT([DateField],"mmmm") TEXT([DateField],"mmm") |
September Sep |
| Get Formatted Year | TEXT([DateField],"yyyy") TEXT([DateField],"yy") |
2011 11 |
| 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))- WEEKDAY([Date])+1)/7,0)+1 |
5 |
| 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) {100} |
| 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) {0} |
| 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 campin"} |
| 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"} |
| Operators | ||
| 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", "Assigned") |
| 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 |
ISNONTEXT (Value) | ISNONTEXT(2) {True}, ISNONTEXT("Two") {False} |
| Returns True if Value is a Number, False otherwise |
ISNUMBER (Value) | ISNUMBER(2) {True}, ISNUMBER("Two") {False} |
| Returns True if Value is Text | ISTEXT (Value) | ISTEXT(2) {False}, ISTEXT("Two") {True} |
References
No comments:
Post a Comment