SharePoint Calculated Column Formulas & Functions - The Essential Guide

AND function

Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE.

SyntaxAND(logical1,logical2,...) Logical1, logical2, ... are 1 to 30 conditions you want to test that can be either TRUE or FALSE.

Remarks

1. The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be column references that contain logical values.

2. If a column reference argument contains text, AND returns the #VALUE! error value; if it is empty, it returns FALSE.

3. If the arguments contain no logical values, AND returns the #VALUE! error value.

Example 1

FormulaDescription (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

Col1Col2FormulaDescription (Result)50104=AND(1<, <100)Because 50 is between 1 and 100 (Yes)50104=IF(AND(1<, <100), , "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.)50104=IF(AND(1<, <100), , "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, , ...)
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.):
 

1. 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

1. 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.

2. If an array or reference argument contains text or empty cells, those values are ignored.

3. If the specified range contains no logical values, OR returns the #VALUE! error value.

4. 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 FormulasGet Week of the year =DATE(YEAR(),MONTH(),DAY())+0.5-WEEKDAY(DATE(YEAR(),MONTH(),DAY()),2)+1 First day of the week for a given date: =-WEEKDAY()+1 Last day of the week for a given date: =+7-WEEKDAY() First day of the month for a given date: =DATEVALUE(“1/”&MONTH()&”/”&YEAR()) Last day of the month for a given year (does not handle Feb 29). Result is in date format: =DATEVALUE (CHOOSE(MONTH(),31,28,31,30,31,30,31,31,30,31,30,31) &”/” & MONTH()&”/”&YEAR()) Day Name of the week : e.g Monday, Mon =TEXT(WEEKDAY(), “dddd”) =TEXT(WEEKDAY(), “ddd”) The name of the month for a given date – numbered for sorting – e.g. 01. January: =CHOOSE(MONTH(),”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 Date-Time : =IF(NOT(ISBLANK()),(-)*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, “&Today-DATE(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, "&Today-DATE(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/working-days-weekdays-holidays-sharepoint-calculated-columns/ 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/limiting-sharepoint-workflow-due-dates-to-business-days/">http://dlairman.wordpress.com/2010/10/14/limiting-sharepoint-workflow-due-dates-to-business-days/</a Calculate work days excluding holidays in InfoPath 2010 using SharePoint 2010 and Excel Services: http://www.bizsupportonline.net/blog/2011/02/calculate-work-days-exclude-holidays-infopath-2010-sharepoint-2010-excel-services/ 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, time-permitting, 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/a-foolproof-approach-to-writing-complex-excel-formulas-146641 Make sure your formula is not capping out on one of the following limits: Formula length: 1024 characters Calculation length: 2048 characters Filter length: 256 characters You can avoid broken formulas by adhering to this basic Excel guidance (only SharePoint-related sections listed below):

1. Start every function with an equal sign (=)

2. Match all opening and closing parentheses

3. Enter all required arguments

4. Use unformatted numbers in formulas

5. Use the right cell data type (in the SharePoint realm, this would mean, use the right List/Library Column type)

6. Use the * symbol to multiply numbers

7. Use quotation marks around text in formulas

8. Don’t nest more than 64 functions in a formula

9. Enclose sheet names in single quotation marks

10. Avoid dividing numeric values by zero

11. Avoid deleting data that’s referenced in formulas

12. Watch formulas and their results

13. Evaluate complex formulas one step at a time

14. Use error checking to detect errors