SharePoint Calculated Column Functions List

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

CalcMaster

There is a Bookmarklet on GitHub

which hooks into the SharePoint Formula editor and does syntax checking and autosave.

ViewMasters

Since SharePoint 2010 a Calculated Column set to datatype=Number can output HTML and JavaScript to a View; for details see my site www.ViewMaster365.com

Tested and tried Functions:

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
Formula Description Output
TEXT ([DateTimeField],'hh:mm:ss') Time Only 08:21:35
TEXT ([DateTimeField],'dddd') Weekday Tuesday
TEXT ([DateTimeField],'ddd') Weekday abbriavted Tue
TEXT ([DateTimeField],'mmmm') Month January
TEXT ([DateTimeField],'mmm') Month abbreviated Jan
TEXT ([DateTimeField],'yyyy') Year 2015
TEXT ([DateTimeField],'yyy') Year abbreviated 15
TEXT ([DateTimeField],'mmmm dd, yyyy') January 21, 2015
TEXT (3.1415679,'000') 21.1
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
Formula Description Output
=IF ( AND ( HOUR ([DateTimeField])<18 , HOUR ([DateTimeField])>6 ) , 'day' , 'night') Test if time is between 6am and 18pm day
=HOUR ( [Column1]-[Column2] ) Hours between two times, !when the difference does not exceed 24!
=INT ( ( [Column2] - [Column1] ) * 24) Total hours between two times
= ( [Column1] - INT ( [Column1] ) ) *24 Convert hours from the standard time format to a decimal number When Column1=11:35 AM, returns the number of hours since 12:00
Date MINUTE ( date )
=IF ( HOUR ([DateTimeField]) < 12 , 'AM' , 'PM')
Returns the Minute value
Formula Description Output
=MINUTE ( [Column2] - [Column1] ) Minutes between two times, !when the difference does not exceed 60!
=INT ( ( [Column2] - [Column1] ) * 1440 ) Total Minutes between two times
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
Formula Description Output
=SECOND ( [Column2] - [Column1] ) Seconds between two times, !when the difference does not exceed 60!
=INT ( ( [Column2] - [Column1] ) * 86400 ) Total Seconds between two times
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.
Formula Description Output
=[Today] + (6 - MOD(WEEKDAY([Today]),7)) + 7 Next Friday Next Friday
TEXT (WEEKDAY ([DateField]),'ddd') (abbreviated) Name of a weekday Mon
WEEKDAY (DATE (YEAR ([DateField]),1,1)) name of january 1st Monday
ROUNDDOWN ( ([DateField]-DATE (YEAR ([DateField]),1,1)+WEEKDAY (DATE (YEAR ([DateField]),1,1))-WEEKDAY ([DateField]+1)/7,0)+1 Shows the weeknumber (US Style) 5
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.
Formula Description Output
=ROUND ( 20.3 ) Rounds Down because .3 is less than .5 20
=ROUND ( 5.9 ) Rounds Up because .9 is greater than .5 6
=ROUND ( -5.9 ) Rounds Down because -.9 is less than -.5 -6
=ROUND ( -5.3987 , 1 ) Rounds Up because -.3 is greater than -.5 -5.3
Calc ROUNDDOWN ( value [ , decimals ] )
=ROUNDDOWN ( [Column1] [ , decimals ] )
Rounds Down to the nearest number with optional decimal.
Formula Description Output
=ROUNDDOWN ( 20.34 ) nearest number 20
=ROUNDDOWN ( 20.34 , 1 ) nearest number 20.3
=ROUNDDOWN ( -5.9 ) nearest number (incorrect -6 in MSDN!) -5
=ROUNDDOWN ( 12.5493 , 2 ) nearest number 12.54
Calc ROUNDUP ( value [ , decimals ] )
=ROUNDUP ( [Column1] [ , decimals ] )
Rounds Up to the nearest number with optional decimal.
Formula Description Output
=ROUNDUP ( 20.3 ) nearest number 21
=ROUNDUP ( -5.9 ) nearest number (incorrect -5 in MSDN!) -6
=ROUNDUP ( 12.5493 , 2 ) nearest number 12.55
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
Formula Description Output
=TRUNC ( 1.6 ) 1