Dustin Schieler Consulting > SharePoint - Calculated Column Formulas

SharePoint Tips & Tricks

Calculated Column Formulas

Jan 23

Written by: Dustin Schieler
1/23/2013 10:41 AM

I thought it would be a good idea to post this list of common formulas for SharePoint Calculated Columns.

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 $ currency, comma thousand separator and 2 decimal places	$1234.56
	*TEXT([Sales],”$#,###.00?);*
	Negative numbers in brackets	(95.99)
	TEXT([Sales],”#,###.00;(#,###.00)”);
OPERATORS
&	Concatenate (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

Trackback Print

Tags:

Categories:

Location: Blogs Parent Separator

SharePoint

0 comment(s) so far...

Dustin Schieler Consulting

Dustin Schieler

Dustin Schieler Consulting Technology Blog

SharePoint Tips & Tricks

Calculated Column Formulas

View & Subscribe

Other IT Tips & Tricks Entries