If your are new to Calculated Column Formulas read the Microsoft introduction to SharePoint Calculations
I collected this list from various resources. MSDN Calculated Field Formulas is incomplete, and sometimes even plain wrong. For example: The MSDN page says the MID function is not supported, but has been supported for a long time.
Multiple functions like SUBSTITUTE state they work in SharePoint Online; but do not!
Check this Microsoft list of Excel functions if you are missing one in the list below. Be aware, this documentation is also not always correct.
Most simple (hardly ever used) Financial functions do work in Formula, I have omitted them for brevity.
I don't do questions/answers on this page. I will answer (allmost all) Calculated Column questions on SharePoint/StackOverflow
Category | Function | Description | ||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Text | CHAR ( asciivalue ) |
=CHAR ( 65 )
Returns the character specified by a number (ASCII code)
|
||||||||||||||||||||||||||||||
Text | CLEAN ( string ) |
=CLEAN ( "column" )
Removes all nonprintable characters from a string
|
||||||||||||||||||||||||||||||
Text | CODE ( string ) |
=CODE ("A")
Returns the ASCII numeric code for the first character in a text string.
|
||||||||||||||||||||||||||||||
Text | CONCATENATE( str1 , [ .. , str30 ]) |
=CONCATENATE( str1 , [ .. , str30 ])
Join all parameters together as one string. & (ampersand) also works as string concatenation: str1 & str2 & str3
|
||||||||||||||||||||||||||||||
Text | DOLLAR ( value ) |
=DOLLAR ( "column" )
Converts a number to text using currency format, with the decimals rounded to the specified place. The format used is $#,##0.00_); ($#,##0.00). Use USDOLLAR Function to always use $ notation
|
||||||||||||||||||||||||||||||
Text | FIND ( needle , haystack [ , start ] ) |
=FIND ( "column" , "a string or column" [ , start_num ] )
Returns the number of the character at which a specific character or text string is first found, beginning at start_num. !!Use SEARCH for NON Case-Sensitive searches!!
|
||||||||||||||||||||||||||||||
Text | LEFT ( string [ , length ] ) |
=LEFT ( 'applepie' , 5 )
returns the first character or characters in a text string: apple, based on the number of characters you specify. Default is 1 character (apple)
|
||||||||||||||||||||||||||||||
Text | LEN ( string ) |
=LEN ('13 characters')
returns the number of characters in a text string
|
||||||||||||||||||||||||||||||
Text | LOWER ( string ) |
=LOWER ( "Hello World!" )
Converts text to lowercase. -> hello world!
|
||||||||||||||||||||||||||||||
Text | MID ( string , position , length ) |
=MID ( '6 applepies' , 3 , 5 )
returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify
|
||||||||||||||||||||||||||||||
Text | PROPER( sentence string ) |
=PROPER ( "hello world!" )
Capitalizes the first letter in each word of a text value. Outputs: Hello World!
|
||||||||||||||||||||||||||||||
Text | REPLACE ( string , pos , length , new ) |
=REPLACE ( string , position , length , newtext )
replaces part of a text string, based on the number of characters you specify. Alas a Substitute function is not available in SharePoint, you have to use nested Replace calls.
|
||||||||||||||||||||||||||||||
Text | REPT ( string , count ) |
=REPT ( "x" , 3 )
Repeates an x 3 times to xxx
|
||||||||||||||||||||||||||||||
Text | RIGHT ( string [ , length ] ) |
=RIGHT ( 'applepie' , 3 )
returns the last characters in a text string, based on the number of characters you specify (pie)
|
||||||||||||||||||||||||||||||
Text | ROMAN ( year , [form] ) |
=ROMAN (2015, [form] )
Converts an arabic numeral to roman, as text.
|
||||||||||||||||||||||||||||||
Text | SEARCH ( needle , haystack [ , start ] ) |
=SEARCH ( "column" , "a string or column" [ , start_num ] )
Returns the number of the character at which a specific character or text string is first found, beginning at start_num. !! Use FIND for Case-Sensitive searches!!
|
||||||||||||||||||||||||||||||
Text | T ( value ) |
=T ( "2100")
If value is text, T returns value. If value does not refer to text, T returns a empty textstring. So T( 2100 ) returns an empty string.
|
||||||||||||||||||||||||||||||
Text | TEXT ( date/number , format ) |
TEXT ([DateTimeField],'format')
Prints a DateTime field as text
|
||||||||||||||||||||||||||||||
Text | TRIM ( string ) |
=TRIM ( " column " )
Removes/trims spaces at beginning and end of a string -> 'column'
|
||||||||||||||||||||||||||||||
Text | UPPER ( string ) |
=UPPER ( "Hello World!" )
Converts text to uppercase. -> HELLO WORLD!
|
||||||||||||||||||||||||||||||
Text | VALUE ( numberstring ) |
=VALUE ( "2100")
Converts a text string that represents a number to a number, returns #VALUE! on error
|
||||||||||||||||||||||||||||||
Logic | AND ( value1 , value2 , [ .. , value30] ) |
=AND ( [Col]>9 , [Col]<12 )
Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE. Maximum of 30 parameters.
|
||||||||||||||||||||||||||||||
Logic | CHOOSE ( index , value1 [ .. , value29] ) |
=CHOOSE (2 , [Col1] , [Col2] , ... , [Col29] )
Will return Col2 (the index number) to a maximum of 29 Choices (first parameter is the index selector)
|
||||||||||||||||||||||||||||||
Logic | EXACT ( string , string ) |
EXACT ( "Hello World" , "HELLO WORLD")
Compares two text strings and returns the logical value TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive.
|
||||||||||||||||||||||||||||||
Logic | IF ( condition , true , false ) |
=IF ([Column1]=15, "OK", "Not OK")
If the value in Column1 equals 15, return the string OK
|
||||||||||||||||||||||||||||||
Logic | ISBLANK ( value ) |
=ISBLANK ( column )
Returns TRUE/YES for value not defined or an empty string
|
||||||||||||||||||||||||||||||
Logic | ISERROR( calculation ) |
=IF (ISERROR ([Column1]/[Column2]), "Invalid Calculation", [Column1]/[Column2])
Returns the text Invalid Calculation if the calculation value is an error, or execute the calculation when valid. Use ISERR() to testfor all errors except #N/A
|
||||||||||||||||||||||||||||||
Logic | ISEVEN ( value ) |
ISEVEN ( [Column] )
Returns TRUE if number is even, or FALSE if number is odd.
|
||||||||||||||||||||||||||||||
Logic | ISLOGICAL ( value ) |
ISLOGICAL ( value )
Returns TRUE if value is a boolean True (Yes/No columns)
|
||||||||||||||||||||||||||||||
Logic | ISNA ( value ) |
=ISNA ( column )
Tests if value is a N/A Error value
|
||||||||||||||||||||||||||||||
Logic | ISNONTEXT ( value/string ) |
ISNONTEXT ( [Column] )
Returns Yes if Column is Not a Text. Note: This function returns Yes for Blank values; the Function ISNUMBER returns No for Blank values
|
||||||||||||||||||||||||||||||
Logic | ISNUMBER ( value/string ) |
ISNUMBER ( [Column] )
Returns Yes if Column is a Number. Note: This function returns No for Blank values; the Function ISNONTEXT returns Yes for Blank values
|
||||||||||||||||||||||||||||||
Logic | ISODD ( value ) |
ISODD ( [Column] )
Returns TRUE if number is odd, or FALSE if number is even.
|
||||||||||||||||||||||||||||||
Logic | NOT ( value ) |
=NOT (15+9=24)
Returns Yes or No (True/False) for a given statement. Is 15 plus 9 not equal to 24? (No)
|
||||||||||||||||||||||||||||||
Logic | OR ( value1 , value 2 , [ .. , value30] ) |
=OR (15>9, 15<8)
Returns Yes if any argument is TRUE; returns No if all arguments are FALSE.
|
||||||||||||||||||||||||||||||
Date | DATE ( year , month , day ) |
=DATE ( YEAR ([Column1]) , MONTH ([Column1])+3 , DAY ([Column1]) )
Returns a DateValue for given Year, Month, Day. Adds 3 months to the date in Column1
|
||||||||||||||||||||||||||||||
Date | DATEDIF ( date , date , unit ) |
=DATEDIF ([Column1],[Column2],'d')
Returns the difference in days between dates in columns 1 and 2. 3rd parameter can be 'ym' or 'yd' to count the months or day excluding the year part.
|
||||||||||||||||||||||||||||||
Date | DATEVALUE ( date ) |
=DATEVALUE ('12/31/2015')
Converts a date that is stored as text to a Date serial number
|
||||||||||||||||||||||||||||||
Date | DAY ( date ) |
=DAY ([Column1])
Returns the day number 21 if Column is 11/21/2014
|
||||||||||||||||||||||||||||||
Date | DAYS360 ( date1 , date2 ) |
=DAYS360 ()
returns the number of days between two dates based on a 360-day year (twelve 30-day months) used in some accounting calculations.
|
||||||||||||||||||||||||||||||
Date | HOUR ( date ) |
=IF ( HOUR ([DateTimeField]) < 12 , 'AM' , 'PM')
Returns the Hour value
|
||||||||||||||||||||||||||||||
Date | MINUTE ( date ) |
=IF ( HOUR ([DateTimeField]) < 12 , 'AM' , 'PM')
Returns the Minute value
|
||||||||||||||||||||||||||||||
Date | MONTH ( date ) |
=MONTH ([Column1])
Returns the month 11 if Column is 11/21/2014
|
||||||||||||||||||||||||||||||
Date | NOW () |
=NOW ()
Returns the serial number of today's date + time. Note! See Today()
|
||||||||||||||||||||||||||||||
Date | SECOND ( date ) |
=IF ( HOUR ([DateTimeField]) < 12 , 'AM' , 'PM')
Returns the Seconds value
|
||||||||||||||||||||||||||||||
Date | TODAY () |
=TODAY ()
Returns the serial number of today's date. Note! The SharePoint TODAY Constant can not be used in Formulas and the TODAY() function will only re-calculated WHEN the Item is updated by a user (or a Workflow!). So it can not be used for day calculations as it does in Excel!
|
||||||||||||||||||||||||||||||
Date | WEEKDAY ( date ) |
=WEEKDAY ([DateField],[startday])
Returns the sequential number of a given date. Optional startday=1 (default) starts week on sunday=1; startday=2 week starts on monday (so sunday=7); startday=3 week starts on tuesday (sunday=6). MSDN documentation list more values, they are not valid in SharePoint.
|
||||||||||||||||||||||||||||||
Date | YEAR ( date ) |
=YEAR ([Column1])
Returns the fullyear 2014 if Column is 11/21/2014
|
||||||||||||||||||||||||||||||
Calc | AVERAGE ( value1 , [ .. , value30] ) |
=AVERAGE ( [Column1] , [Column2] , ... , [Column30] )
Average of the values of three columns.
|
||||||||||||||||||||||||||||||
Calc | COUNT ( value1 , [ .. , value30] ) |
=COUNT ( [Column1] , [Column2] , ... , [Column30] )
Counts the number of columns that contain Numeric values. Excluding Date,Text and Null/Blank,Error or Logical values See COUNTA for counting Text values
|
||||||||||||||||||||||||||||||
Calc | COUNTA ( value1 , [ .. , value30] ) |
=CHOOSE ( [Col1] , [Col2] , [Col3] , [Col4] , ... , [Col30] )
counts the number of cells that are not empty in a range. Maximum of 30 columns
|
||||||||||||||||||||||||||||||
Calc | EVEN ( value ) |
=EVEN ( [Column1] )
Rounds Up to the nearest Even number, Even ( 12.4 ) -> 14
|
||||||||||||||||||||||||||||||
Calc | FIXED ( value , decimals , T/F ) |
=FIXED ( [Column] , Nr , optional: TRUE )
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text. The optional TRUE value prevents from including commans in the returned text.
|
||||||||||||||||||||||||||||||
Calc | INT ( value ) |
=INT ( [Column1] )
Rounds a number Down to the nearest integer
|
||||||||||||||||||||||||||||||
Calc | MAX ( value1 , [ .. , value30] ) |
=MAX ( [Column1] , [Column2] , ... , [Column30] )
Returns the Largest value of all columns. Use MAXA to include True (1) or False and Text values (0)
|
||||||||||||||||||||||||||||||
Calc | MAXA ( value1 , [ .. , value30] ) |
=MAXA ( [Column1] , [Column2] , ... , [Column30] )
Returns the Largest value of all columns.
|
||||||||||||||||||||||||||||||
Calc | MEDIAN ( value1 , [ .. , value30] ) |
=MEDIAN ( [Column1] , [Column2] , ... , [Column30] )
Median of the values of all columns.
|
||||||||||||||||||||||||||||||
Calc | MIN ( value1 , [ .. , value30] ) |
=MIN ( [Column1] , [Column2] , ... , [Column30] )
Returns the smallest value of all columns. Use MINA to INclude Logical and Text values.
|
||||||||||||||||||||||||||||||
Calc | MINA ( value1 , [ .. , value30] ) |
=MINA ( [Column1] , [Column2] , ... , [Column30] )
Returns the smallest value of all columns. Use MIN to EXclude Logical and Text values.
|
||||||||||||||||||||||||||||||
Calc | MOD ( value , divisor ) |
=MOD ( 7 , 4 )
(Modulo) Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
|
||||||||||||||||||||||||||||||
Calc | MODE ( value1 , [ .. , value30] ) |
=MODE ( [Column1] , [Column2] , ... , [Column30] )
Returns the most frequently occurring, or repetitive, value in the argument list.
|
||||||||||||||||||||||||||||||
Calc | ODD ( value ) |
=ODD ( [Column1] )
Rounds Up to the nearest Odd number, Odd ( 12.4 ) -> 13
|
||||||||||||||||||||||||||||||
Calc | POWER ( value , power ) |
=POWER ( 5 , 3 )
Same as: 5^3 , Calculates 5x5x5
|
||||||||||||||||||||||||||||||
Calc | PRODUCT ( value1 , [ .. , value30] ) |
=PRODUCT ( [Column1] , [Column2] , ... , [Column30] )
Muliplies multiple value.
|
||||||||||||||||||||||||||||||
Calc | ROUND ( value [ , decimals ] ) |
=ROUND ( [Column1] [ , decimals ] )
Rounds Up or Down to the nearest fraction with optional decimal.
|
||||||||||||||||||||||||||||||
Calc | ROUNDDOWN ( value [ , decimals ] ) |
=ROUNDDOWN ( [Column1] [ , decimals ] )
Rounds Down to the nearest number with optional decimal.
|
||||||||||||||||||||||||||||||
Calc | ROUNDUP ( value [ , decimals ] ) |
=ROUNDUP ( [Column1] [ , decimals ] )
Rounds Up to the nearest number with optional decimal.
|
||||||||||||||||||||||||||||||
Calc | SIGN( value ) |
=SIGN ( -7 )
Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.
|
||||||||||||||||||||||||||||||
Calc | SUM ( value1 , [ .. , value30] ) |
=SUM ( [Column1] , [Column2] , ... , [Column30] )
Adds the values of three columns.
|
||||||||||||||||||||||||||||||
Calc | INT ( value ) |
=TRUNC ( [Column1] )
Truncates a number
|