Skip to content

May 25, 2017

cash register

SharePoint Calculated Column Field Formulas – Alphabetically

I’ve been working on some new course material and I wanted to verify the list of functions that could be used in a calculated column and realized that the list that was available from Microsoft wasn’t in a meaningful order – so I’ve alphabetized the list. I included it here for those who want it.

  • ABS() – Absolute. Remove the negative if present
  • AND() – Logical And. Return true if both clauses return true.
  • AVERAGE() – Average value. The average of all the provided parameters
  • CONCATENATE() – String concatenation. Concatenates every parameter provided and returns it as a single string.
  • COUNT() – Count the values. Returns the number of values provided.
  • COUNTA() – Count the non-null values. Returns the number of non-null values returned.
  • DATE() – Create a date. Given a month, day, and year return a date
  • DATEDIF() – Difference between dates. Given two dates, return the difference between them.
  • DAY() – Day of month. Given a date return the day of month.
  • EVEN() -Round up to the nearest even number.
  • EXACT() – Exact comparison. Given two values return true if they are exactly the same
  • FIND() – Find a string in another. Returns the position of the first string in the second or null if not found
  • HOUR() – Returns the hour of day. Given a date time column returns the hour of day
  • IF() – Return results based on a comparison. If the first parameter is true return the second parameter otherwise return the third parameter
  • INT() – Returns an integer. Given a number returns the integer portion.
  • ISERROR()- Is the parameter an error. Returns true if the parameter is an error and false if it is not.
  • ISNUMBER() – Is the parameter a number. Returns true if the parameter results in a number and false if not.
  • LEN() – Length of a string. Returns the number of characters in a string.
  • LEFT() – Left portion of a string. Returns from the first parameter the number of characters specified in the second.
  • LOWER() – Convert string to all lower case. Returns the string provided but in all lower case.
  • MAX() – Maximum. Returns the maximum of the provided parameters.
  • MEDIAN() – Median. Returns the number at the median of the provided set of parameters.
  • MIN() – Minimum. Returns the smallest of the provided parameters.
  • MINUTE() – Minutes after the hour. Returns the number of minutes after the hour from a provided date time parameter
  • MONTH() – Month of year. Given a date returns the month of year.
  • NOT() – Logical not. Returns true if the parameter results in false and false if the parameter results in true.
  • ODD() – Round up to the next odd number.
  • OR() – Logical Or. Returns true if either of the parameters result in true.
  • POWER() – Exponent. Raises the first number to the power (exponent) of the second.
  • PRODUCT() – Multiply. Multiplies the provided parameters
  • PROPER() – Proper case a string. Initial capitalize the string.
  • REPT() – Repeat character. Repeat the string provided in the first parameter the number of times specified in the second parameter.
  • RIGHT() – Right portion of the string. Return from the first parameter string the number of characters specified in the second parameter – from the end of the string.
  • ROUND() – Round. Round the number to the nearest whole number (up or down).
  • ROUNDDOWN() – Round down only. Round the number down to the nearest integer.
  • ROUNDUP() – Round up only. Round the number up to the nearest integer.
  • SECOND() – Seconds after the minute. Given a date time parameter, return the number of seconds after the minute.
  • SUM() – Arithmetic sum. Add the parameters together
  • TEXT() – Convert to string. Format the first parameter according to the format specification in the second parameter.
  • TRIM() – Remove spaces. Removes spaces from the front and end of a string.
  • UPPER() – Upper case string. Converts a string to all upper case.
  • WEEKDAY() – Day of Week. Returns the day of week for a date time parameter
  • YEAR() – Year. Returns the year for a given date time parameter

There you have it, the list of functions that are supported in calculated columns.

Recent Posts

Public Speaking