SharePoint Calculated Column Formulas & Functions – The Essential Guide
Written by Keith Tuomi on . Posted in Lists, Office 365, SharePoint 2010, SharePoint Online. 147 Comments
SharePoint uses pretty much the same engine as Excel for it’s calculated columns and field validation stuff. There are some exceptions, such as NETWORKDAYS and some other functions not being included. Try prototyping your formulas in Excel first and then applying them to SharePoint.The goal of this page is to become the definitive list of formulas and operations used in SharePoint Calculated Columns. I try to answer all formula requests but i’d rather see you learn the fundamentals than get a quick fix, so please read all references carefully before submitting a scenario via the comments.
In terms of “programming is hard” – there’s not a whole lot to this stuff as it is very well defined and encapsulated. Once you master it, you will be the new Office rockstar careers have been built on this.
Did I miss one? Please drop a note in the comments and i’ll get it added!
Type Explanation Output NUMBERS 0 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^364 /, +, , * 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]) Q12012 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)+15 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 201204 TEXT([Created], “yyyymm”) 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 nonprintable 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 numberlike values SUM(0, 2, “26”, 100, TRUE) 128 MINA (Number1, Number2, …) Gets the smallest of the numbers, including nonnumber 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 onnumber 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 nonNumber values COUNT(5, 0,TRUE) 2 AVERAGEA (Num1, Num2, …) Averages the Numbers, nonNumber values are interpreted AVERAGEA(5,0, TRUE) 2 AVERAGE (Num1, Num2, …) Averages the Numbers, ignoring nonNumber 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 AND function
Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE.
Syntax
AND(logical1,logical2,…)
Logical1, logical2, … are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
Remarks
 The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be column references that contain logical values.
 If a column reference argument contains text, AND returns the #VALUE! error value; if it is empty, it returns FALSE.
 If the arguments contain no logical values, AND returns the #VALUE! error value.
Example 1
Formula Description (Result) =AND(TRUE, TRUE) All arguments are TRUE (Yes) =AND(TRUE, FALSE) One argument is FALSE (No) =AND(2+2=4, 2+3=5) All arguments evaluate to TRUE (Yes) Example 2
Col1 Col2 Formula Description (Result) 50 104 =AND(1<[Col1], [Col1]<100) Because 50 is between 1 and 100 (Yes) 50 104 =IF(AND(1<[Col2], [Col2]<100), [Col2], “The value is out of range.”) Displays the second number, if it is between 1 and 100, otherwise displays a message (The value is out of range.) 50 104 =IF(AND(1<[Col1], [Col1]<100), [Col1], “The value is out of range.”) Displays the first number, if it is between 1 and 100, otherwise displays a message (50) OR function
Description
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax
OR(logical1, [logical2], ...)
The OR function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
 Logical1, logical2, … Logical1 is required, subsequent logical values are optional. 1 to 255 conditions you want to test that can be either TRUE or FALSE.
Remarks
 The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) or references that contain logical values.
 If an array or reference argument contains text or empty cells, those values are ignored.
 If the specified range contains no logical values, OR returns the #VALUE! error value.
 You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER.

Common Date & Time Formulas
Get Week of the year =DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]))+0.5WEEKDAY(DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),2)+1
First day of the week for a given date: =[Start Date]WEEKDAY([Start Date])+1
Last day of the week for a given date: =[End Date]+7WEEKDAY([End Date])
First day of the month for a given date: =DATEVALUE(“1/”&MONTH([Start Date])&”/”&YEAR([Start Date]))
Last day of the month for a given year (does not handle Feb 29). Result is in date format: =DATEVALUE (CHOOSE(MONTH([End Date]),31,28,31,30,31,30,31,31,30,31,30,31) &”/” & MONTH([End Date])&”/”&YEAR([End Date])) Day Name of the week : e.g Monday, Mon =TEXT(WEEKDAY([Start Date]), “dddd”) =TEXT(WEEKDAY([Start Date]), “ddd”)
The name of the month for a given date – numbered for sorting – e.g. 01. January: =CHOOSE(MONTH([Date Created]),”01. January”, “02. February”, “03. March”, “04. April”, “05. May” , “06. June” , “07. July” , “08. August” , “09. September” , “10. October” , “11. November” , “12. December”)
Get Hours difference between two DateTime : =IF(NOT(ISBLANK([End Time])),([End Time][Start Time])*24,0)
Date Difference in days – Hours – Min format : e.g 4days 5hours 10min : =YEAR(Today)YEAR(Created)IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created), DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)MONTH(Created)+IF(AND(MONTH(Today) < =MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today) > =DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),1)))&” months, “&TodayDATE(YEAR(Today),MONTH(Today)IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”
Display SharePoint List Items Age: Create a SharePoint Calculated Column to Display a List Item as “X” Days Old
=YEAR(Today)YEAR(Created)IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)MONTH(Created)+IF(AND(MONTH(Today)
<=MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
>=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),1)))&” months,
“&TodayDATE(YEAR(Today),MONTH(Today)IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”Workdays Logic
Here’s a basic approach for Calculated Columns workdays logic: Working Days, Weekdays and Holidays in SharePoint Calculated Columns: http://blog.pentalogic.net/2008/11/workingdaysweekdaysholidayssharepointcalculatedcolumns/
This shows how to combine an approach like that into Workflow logic: Limiting SharePoint Workflow Due Dates to Business Days: http://dlairman.wordpress.com/2010/10/14/limitingsharepointworkflowduedatestobusinessdays/”>http://dlairman.wordpress.com/2010/10/14/limitingsharepointworkflowduedatestobusinessdays/</a
Calculate work days excluding holidays in InfoPath 2010 using SharePoint 2010 and Excel Services: http://www.bizsupportonline.net/blog/2011/02/calculateworkdaysexcludeholidaysinfopath2010sharepoint2010excelservices/
A blanket warning about anything to do with time & date programming and specific ranges like work days – always keep in mind that there’s: – Your Application logic’s concept of work week – SharePoint’s regional time and date, locale & work week settings – The Users desktop/profile settings such as region, timezone, personal variations such as different work days

DON’T FORGET – if you really can’t seem to come up with a formula that suits your needs based on what’s available in Excel/SharePoint formulas and functions, chances are you can achieve what you need with either:
Option A – (SharePoint 2013 only) Use JSLink to do your calculations in JavaScript on the client side. Here’s an article on the basics of implementing JSLink in SharePoint 2013. After that, you have the whole JavaScript language at your disposal to do calculations on the fly. There are plenty of JS/Jquery libraries such as this one that can handle the heavy lifting for you.
Option B – (SharePoint 2010/2007 only) Whip up some custom XSLT as described here and make your own custom columns that play by the rules you define there.
If you are still stumped after reading the information on this page, feel free to drop your question in the comments section at the bottom of the page and i’ll do my best, timepermitting, to help you out. This stuff takes stubbornness more than brains so chances are, if you stick it out, you can solve the scenario on your own and learn for next time. Please give it a go on your own first before posing a question here!
The hardest part of Calculated columns for most people is figuring out how to combine multiple functions and values together into one formula the following article is great for explaining that part:
http://searchengineland.com/afoolproofapproachtowritingcomplexexcelformulas146641Make sure your formula is not capping out on one of the following limits:
Formula length: 1024 characters
Calculation length: 2048 characters
Filter length: 256 charactersYou can avoid broken formulas by adhering to this basic Excel guidance (only SharePointrelated sections listed below):
 Start every function with an equal sign (=)
 Match all opening and closing parentheses
 Enter all required arguments
 Use unformatted numbers in formulas
 Use the right cell data type (in the SharePoint realm, this would mean, use the right List/Library Column type)
 Use the * symbol to multiply numbers
 Use quotation marks around text in formulas
 Don’t nest more than 64 functions in a formula
 Enclose sheet names in single quotation marks
 Avoid dividing numeric values by zero
 Avoid deleting data that’s referenced in formulas
 Watch formulas and their results
 Evaluate complex formulas one step at a time
 Use error checking to detect errors