SharePoint Calculated Columns

I thought it would be a good idea to post this list of common formulas for SharePoint Calculated Columns. I have found myself coming back to this page a lot, over the years. I hope it might help you as well. There are a ton of other common ones, I’m sure. If you think of any good ones to add please let me know at!

ProfitShows the percentage profit on a sale (tick “Show as percentage”)10%
MarkupGives a price from a cost and a percentage markup$120.00
CommissionGives the commission due on a sale (based on a commission %)$25.00
FormattingFormatted with $ currency, comma thousand separator and 2 decimal places$1234.56
 Negative numbers in brackets(95.99)
&Concatenate (put two text values or fields together) 4 & “3?43
^Power (e.g. [Field]^2 = Squared) 4^364
/, +, -, *Divide, Add, Subtract, Multiply 
= (Equal to)> (Greater than)>= (Greater than or equal to)
<> Not equal to)< (Less than)<= (Less than or equal to)
Time onlyTEXT([DateTimeField],”hh:mm:ss”)01:21:51
CombinationsTEXT([DateField],”mmmm dd, yyyy”October 17, 2012
Fiscal YearShows which fiscal year a date falls in (1st October) 
 FY & IF(DATE(YEAR([Date]), 10, 1)>[Date], YEAR([Date]), YEAR([Date])+1)FY 2012
SeasonShows 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
QuarterShows which quarter a date falls in 
 Q & INT((MONTH([Date])-1)/3)+1Q1
 Q & INT((MONTH([Date])-1)/3)+1 & “-” & YEAR([Date])Q1-2012
Week NumberShows the week number (US style) 
 ROUNDDOWN(([Date]-DATE(YEAR([Date]),1,1)+ WEEKDAY(DATE(YEAR([Date]),1,1))-WEEKDAY([Date])+1)/7,0)+15
Week CommencingShows the date of the first day of the week (useful for grouping by week) 
Day/NightShows whether time is day or night 
AM/PMShows whether a time is AM or PM 
 IF(HOUR([Time]) < 12,”AM”,”PM”)PM
ModifiedShows whether an item has been modified since creation 
 IF([Modified] > [Created], “Changed”, “Original”)Changed
Marks out of tenGives general comments on a mark out of ten 
Random StringChooses 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 (Value, Format)Converts Value to a Text value, using Format2012|04
 TEXT([Created], “yyyy|mm”) 
REPT (Text, Number)Repeats Text the given Number of timesHelloHelloHello
FIXED (Num, Dec, NoCommas)Returns Number with the given number of decimals as text (commas optional) 
LEN (Text)The length of Text4
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 
UPPER (Text)Text in upper case 
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 
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 
EXACT (Text1, Text2)Checks if two text values are identical, returns boolean 
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 
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 
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 
TRUNC (Number)Returns Number with decimals removed 
SQRT (Number)Returns the square root 
SIGN (Number)Returns -1 for negative numbers, 1 for positive, and 0 when 0 
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 
ODD (Number)Rounds Number up to the nearest odd number 
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 
ABS (Number)Makes a number positive if it is negative 
AND (Condition1, Condition2)Returns True if both conditions are 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 
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
ISTEXT (Value)Returns True if Value is Text 
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 
ISNA (Value)Returns True if Value returns error #N/A, otherwise False 
ISLOGICAL (Value)Returns True if Value returns a logical value (True or False), False otherwise 
ISERR (Value)Returns True if Value returns an error (except #N/A), otherwise False 
ISBLANK (Value)Returns True if Value is empty, otherwise False 
 IF(ISBLANK([Attendee])Needs Attendee
Lookup columnsNot supported 
[ID]Only works on column addition/update, will not work from then on 
[Today] and [Me]Only available in default columns