SharePoint Calculated Column Formulas

For reference:

http://yalla.itgroove.net/2012/09/sharepoint-calculated-column-formulas/

Type Explanation Output
NUMBERS
Profit Shows the percentage profit on a sale (tick “Show as percentage”) 10%
([Price]-[Cost])/[Cost]
Markup Gives a price from a cost and a percentage markup $120.00
[Cost]*(1+[Markup])
Commission Gives the commission due on a sale (based on a commission %) $25.00
[Sale]*[Commission]
Formatting Formatted with $ curency, comma thousand seperator and 2 decimal places $1234.56
TEXT([Sales],”$#,###.00″);
Negative numbers in brackets (95.99)
TEXT([Sales],”#,###.00;(#,###.00)”);
OPERATORS
& Concatanate (put two text values or fields together) 4 & “3″ 43
^ Power (e.g. [Field]^2 = Squared) 4^3 64
/, +, -, * Divide, Add, Subtract, Multiply
RELATIONAL OPERATORS
= (Equal to) > (Greater than) >= (Greater than or equal to)
<> Not equal to) < (Less than) <= (Less than or equal to)
DATE AND TIME
Time only TEXT([DateTimeField],”hh:mm:ss”) 01:21:51
Weekday TEXT([DateField],”dddd”) Wednesday
TEXT([DateField],”ddd”) Wed
Month TEXT([DateField],”mmmm”) October
TEXT([DateField],”mmm”) Oct
Year TEXT([DateField],”yyyy”) 2012
TEXT([DateField],”yy”) 12
Combinations TEXT([DateField],”mmmm dd, yyyy” October 17, 2012
Fiscal Year Shows which fiscal year a date falls in (1st October)
FY & IF(DATE(YEAR([Date]), 10, 1)>[Date], YEAR([Date]), YEAR([Date])+1) FY 2012
Season Shows which season a date falls in. Takes into account one month offset from quarter.
CHOOSE(INT((MOD(MONTH(When)+1,12)/4))+ 1,”Winter”,”Spring”,”Summer”,”Autumn”) Spring
Quarter Shows which quarter a date falls in
Q & INT((MONTH([Date])-1)/3)+1 Q1
Q & INT((MONTH([Date])-1)/3)+1 & “-” & YEAR([Date]) Q1-2012
Week Number Shows the week number (US style)
ROUNDDOWN(([Date]-DATE(YEAR([Date]),1,1)+ WEEKDAY(DATE(YEAR([Date]),1,1))-WEEKDAY([Date])+1)/7,0)+1 5
Week Commencing Shows the date of the first day of the week (useful for grouping by week)
[Date]-WEEKDAY([Date])+1 3/4/2012
Day/Night Shows whether time is day or night
IF(AND(HOUR([Time])>6,HOUR([Time])<18),”Day”,”Night”) Day
AM/PM Shows whether a time is AM or PM
IF(HOUR([Time]) < 12,”AM”,”PM”) PM
OTHER
Modified Shows whether an item has been modified since creation
IF([Modified] > [Created], “Changed”, “Original”) Changed
Marks out of ten Gives general comments on a mark out of ten
CHOOSE(INT([Marks]/3),”Bad”,”Poor”,”Good”,”Great”) Great
Random String Chooses a string at random, based on the time (in seconds)
CHOOSE(MOD(TEXT(Created,”s”),2)+1,”String A”,”String B”, “String C”) String C
TEXT
TEXT (Value, Format) Converts Value to a Text value, using Format 2012|04
TEXT([Created], “yyyy|mm”)
REPT (Text, Number) Repeats Text the given Number of times HelloHelloHello
REPT(“Hello”,3)
FIXED (Num, Dec, NoCommas) Returns Number with the given number of decimals as text (commas optional)
FIXED(2044.23,1,TRUE) 2044.23
FIXED(2044.23,0,FALSE) 2,044
LEN (Text) The length of Text 4
LEN(“Hola”)
LEFT (Text, Number) Return X characters from the left
LEFT(“The Quick Brown Fox”, 5) The Q
RIGHT (Text, Number) Return X characters from the right
RIGHT(“The Quick Brown Fox”, 5) n Fox
MID (Text, Num1, Num2) Returns Number2 characters from the middle of Text, starting at Number1
MID(“The Quick Brown Fox”, 4, 15) Quick Brown
SEARCH (Text1, Text2, Num) Returns the index of Text1 within Text2,starting the search at index Number
SEARCH(“Banana”, “Banana Banana”, 4) 8
LOWER (Text) Text in lower case
LOWER(“Hello”) hello
UPPER (Text) Text in upper case
UPPER(“Hello”) HELLO
PROPER (Text) Capitalize first letter of each word
PROPER(“good morning”) Good Morning
TRIM (Text) Removes spaces from the start and end
TRIM(” Hello “) Hello
CLEAN (Text) Returns Text without non-printable characters added by clipboard or similar
CLEAN(“String1? String2??”) String1 String2
REPLACE (T1, N1, N2, T2) Replaces Number2 characters starting at Number1 from Text1 with Text2
REPLACE(“Hello”,2,4,”i”) Hi
CONCATENATE (T1, T2, …) Combines the string values together into one string
CONCATENATE(“A”,” Fine “,”Morning”) A Fine Morning
DOLLAR (Number, Decimals) Converts number to currency text, with the given number of decimals
DOLLAR(11.267,2) $11.27
EXACT (Text1, Text2) Checks if two text values are identical, returns boolean
EXACT(“Hello”,”hello”) False
MATH
SUM (Number1, Number2, …) Returns the total of all Numbers and number-like values
SUM(0, 2, “26″, 100, TRUE) 128
MINA (Number1, Number2, …) Gets the smallest of the numbers, including non-number values
MINA(0, 2, “26″, 100, “MyString”, TRUE) 0
MIN (Number1, Number2, …) Gets the smallest of the numbers, including Text fields containing numbers
MIN(0, 1, “26″, 100) 0
MAXA (Number1, Number2, …) Gets the largest of the numbers, including on-number values
MAXA(0, 2, “26″, 100, “MyString”, TRUE) 100
COUNTA (Value1, Value2, …) Counts all values, including empty text (“”), ignoring empty columns
COUNTA(5, 0,TRUE) 3
COUNT (Num1, Num2, …) Averages the Numbers, ignoring non-Number values
COUNT(5, 0,TRUE) 2
AVERAGEA (Num1, Num2, …) Averages the Numbers, non-Number values are interpreted
AVERAGEA(5,0, TRUE) 2
AVERAGE (Num1, Num2, …) Averages the Numbers, ignoring non-Number values
AVERAGE(10, 0, “”, “0″) 5
VALUE (Text) Converts Text to a Number, Date or Time, according to its format
VALUE(“00:05″) 00:05
TRUNC (Number) Returns Number with decimals removed
TRUNC(14.999999) 14
SQRT (Number) Returns the square root
SQRT(25) 5
SIGN (Number) Returns -1 for negative numbers, 1 for positive, and 0 when 0
SIGN(-5.2786) -1
ROUNDUP (Num1, Num2) Rounds Number1 to Number2 decimals, always rounding up
ROUNDUP(22.0001, 0) 23
ROUNDDOWN (Num1, Num2) Rounds Number1 to Number2 decimals, always rounding down
ROUNDDOWN(122.492, 1) 122.4
ROUND (Number1, Number2) Rounds Number1 to Number2 decimals
ROUND(221.298, 1) 221.6
PI () Returns Pi to 15 decimal places
PI() 3.14159265358979
ODD (Number) Rounds Number up to the nearest odd number
ODD(1.5) 3
MOD (Number1, Number2) Returns the remainder of Number1 divided by Number2
MOD(5, 4) 1
EVEN (Number) Rounds Number up to the nearest even number
EVEN(0.5) 2
ABS (Number) Makes a number positive if it is negative
ABS(-1) 1
LOGICAL
AND (Condition1, Condition2) Returns True if both conditions are True
AND(4>=3,3>2) True
OR (Condition1, Condition2) Returns True if either condition is True
OR(4>=3, 3<2) True
NOT (Condition1) Returns the opposite to the condition
NOT(1=1) False
CHOOSE(Num, Val1, Val2, …) Returns the value corresponding to the number. Up to 29 values can be used.
CHOOSE(2, “A”, “B”, “C”, “D”) B
IF(Condition, Val1, Val2) If Conditon is true, return Value1, otherwise return Value2
IF([Modified] > [Created], “Changed”, Original) Changed
ERROR & TYPE CHECKING
ISTEXT (Value) Returns True if Value is Text
ISTEXT(99) False
ISNUMBER (Value) Returns True if Value is a Number, oherwise False
ISNUMBER(99) { True
ISNONTEXT (Value) Returns True if Value is not text or is empty, False otherwise
ISNONTEXT(99) True
ISNA (Value) Returns True if Value returns error #N/A, otherwise False
ISERR(#N/A) True
ISLOGICAL (Value) Returns True if Value returns a logical value (True or False), False otherwise
ISLOGICAL(FALSE) True
ISERR (Value) Returns True if Value returns an error (except #N/A), otherwise False
ISERR(#REF!) True
ISBLANK (Value) Returns True if Value is empty, otherwise False
IF(ISBLANK([Attendee]) Needs Attendee
FORBIDDEN COLUMNS
Lookup columns Not supported
[ID] Only works on column addition/update, will not work from then on
[Today] and [Me] Only available in default columns
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s