2008-04-06

SharePoint Calculated Fields Use Excel Formulas.

I was recently tasked with resolving a date issue for a Global Portal. People outside the US were complaining about the date format (MM/DD/YYYY)... I've long been a proponent of ISO dates (YYYY-MM-DD) and 28 day months... OK maybe 28 day months wouldn't be so fun for savants to calculate.
Anyway the solution was obviously to include the month name. My first reaction was to change FLDTYPES.xml, then to create a custom field type, and finally it soon became obvious a calculated field should do the trick.

So I searched a minute for a formula to render the date with the month name in the middle... I didn't find any so I created it.
It is a piece of cake to do it in VB and C#, but what does a calculated field provide? As it turns out quite a lot. Everybody knows your classic [Today], LTrim, etc... but what I didn't know it that it supports a lot of Excel functions (not the VBA ones).

Before we get to the list of functions, here's how to write the month name formula:
=TEXT([DateField],"dd") &"/"& TEXT([DateField],"mmm") &"/"& TEXT([DateField],"yyyy")
or
=DAY([DateField]) &"/"& TEXT([DateField],"mmm") &"/"& YEAR([DateField])

or better
=IF(NOT(ISBLANK([DateField])),DAY([DateField])&" "&TEXT([DateField],"mmm")&" "&YEAR([DateField]),"")
Now here's probably what your here for, a list of SharePoint Calculated Field/Column functions:
source (http://office.microsoft.com/en-us/excel/HP100791861033.aspx)

Date and time functions

FunctionDescription
DATEReturns the serial number of a particular date
DATEVALUEConverts a date in the form of text to a serial number
DAYConverts a serial number to a day of the month
DAYS360Calculates the number of days between two dates based on a 360-day year
EDATEReturns the serial number of the date that is the indicated number of months before or after the start date
EOMONTHReturns the serial number of the last day of the month before or after a specified number of months
HOURConverts a serial number to an hour
MINUTEConverts a serial number to a minute
MONTHConverts a serial number to a month
NETWORKDAYSReturns the number of whole workdays between two dates
NOWReturns the serial number of the current date and time
SECONDConverts a serial number to a second
TIMEReturns the serial number of a particular time
TIMEVALUEConverts a time in the form of text to a serial number
TODAYReturns the serial number of today's date
WEEKDAYConverts a serial number to a day of the week
WEEKNUMConverts a serial number to a number representing where the week falls numerically with a year
WORKDAYReturns the serial number of the date before or after a specified number of workdays
YEARConverts a serial number to a year
YEARFRACReturns the year fraction representing the number of whole days between start_date and end_date

Information functions

FunctionDescription
CELLReturns information about the formatting, location, or contents of a cell
ERROR.TYPEReturns a number corresponding to an error type
INFOReturns information about the current operating environment
ISBLANKReturns TRUE if the value is blank
ISERRReturns TRUE if the value is any error value except #N/A
ISERRORReturns TRUE if the value is any error value
ISEVENReturns TRUE if the number is even
ISLOGICALReturns TRUE if the value is a logical value
ISNAReturns TRUE if the value is the #N/A error value
ISNONTEXTReturns TRUE if the value is not text
ISNUMBERReturns TRUE if the value is a number
ISODDReturns TRUE if the number is odd
ISREFReturns TRUE if the value is a reference
ISTEXTReturns TRUE if the value is text
NReturns a value converted to a number
NAReturns the error value #N/A
TYPEReturns a number indicating the data type of a value

Logical functions

FunctionDescription
ANDReturns TRUE if all of its arguments are TRUE
FALSEReturns the logical value FALSE
IFSpecifies a logical test to perform
IFERRORReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
NOTReverses the logic of its argument
ORReturns TRUE if any argument is TRUE
TRUEReturns the logical value TRUE

Math and trigonometry functions

FunctionDescription
ABSReturns the absolute value of a number
ACOSReturns the arccosine of a number
ACOSHReturns the inverse hyperbolic cosine of a number
ASINReturns the arcsine of a number
ASINHReturns the inverse hyperbolic sine of a number
ATANReturns the arctangent of a number
ATAN2Returns the arctangent from x- and y-coordinates
ATANHReturns the inverse hyperbolic tangent of a number
CEILINGRounds a number to the nearest integer or to the nearest multiple of significance
COMBINReturns the number of combinations for a given number of objects
COSReturns the cosine of a number
COSHReturns the hyperbolic cosine of a number
DEGREESConverts radians to degrees
EVENRounds a number up to the nearest even integer
EXPReturns e raised to the power of a given number
FACTReturns the factorial of a number
FACTDOUBLEReturns the double factorial of a number
FLOORRounds a number down, toward zero
GCDReturns the greatest common divisor
INTRounds a number down to the nearest integer
LCMReturns the least common multiple
LNReturns the natural logarithm of a number
LOGReturns the logarithm of a number to a specified base
LOG10Returns the base-10 logarithm of a number
MDETERMReturns the matrix determinant of an array
MINVERSEReturns the matrix inverse of an array
MMULTReturns the matrix product of two arrays
MODReturns the remainder from division
MROUNDReturns a number rounded to the desired multiple
MULTINOMIALReturns the multinomial of a set of numbers
ODDRounds a number up to the nearest odd integer
PIReturns the value of pi
POWERReturns the result of a number raised to a power
PRODUCTMultiplies its arguments
QUOTIENTReturns the integer portion of a division
RADIANSConverts degrees to radians
ROMANConverts an arabic numeral to roman, as text
ROUNDRounds a number to a specified number of digits
ROUNDDOWNRounds a number down, toward zero
ROUNDUPRounds a number up, away from zero
SERIESSUMReturns the sum of a power series based on the formula
SIGNReturns the sign of a number
SINReturns the sine of the given angle
SINHReturns the hyperbolic sine of a number
SQRTReturns a positive square root
SQRTPIReturns the square root of (number * pi)
SUBTOTALReturns a subtotal in a list or database
SUMAdds its arguments
SUMIFAdds the cells specified by a given criteria
SUMIFSAdds the cells in a range that meet multiple criteria
SUMPRODUCTReturns the sum of the products of corresponding array components
SUMSQReturns the sum of the squares of the arguments
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2Returns the sum of squares of differences of corresponding values in two arrays
TANReturns the tangent of a number
TANHReturns the hyperbolic tangent of a number
TRUNCTruncates a number to an integer

Statistical functions

FunctionDescription
AVEDEVReturns the average of the absolute deviations of data points from their mean
AVERAGEReturns the average of its arguments
AVERAGEAReturns the average of its arguments, including numbers, text, and logical values
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFSReturns the average (arithmetic mean) of all cells that meet multiple criteria.
BETADISTReturns the beta cumulative distribution function
BETAINVReturns the inverse of the cumulative distribution function for a specified beta distribution
BINOMDISTReturns the individual term binomial distribution probability
CHIDISTReturns the one-tailed probability of the chi-squared distribution
CHIINVReturns the inverse of the one-tailed probability of the chi-squared distribution
CHITESTReturns the test for independence
CONFIDENCEReturns the confidence interval for a population mean
CORRELReturns the correlation coefficient between two data sets
COUNTCounts how many numbers are in the list of arguments
COUNTACounts how many values are in the list of arguments
COUNTBLANKCounts the number of blank cells within a range
COUNTIFCounts the number of cells within a range that meet the given criteria
COUNTIFSCounts the number of cells within a range that meet multiple criteria
COVARReturns covariance, the average of the products of paired deviations
CRITBINOMReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQReturns the sum of squares of deviations
EXPONDISTReturns the exponential distribution
FDISTReturns the F probability distribution
FINVReturns the inverse of the F probability distribution
FISHERReturns the Fisher transformation
FISHERINVReturns the inverse of the Fisher transformation
FORECASTReturns a value along a linear trend
FREQUENCYReturns a frequency distribution as a vertical array
FTESTReturns the result of an F-test
GAMMADISTReturns the gamma distribution
GAMMAINVReturns the inverse of the gamma cumulative distribution
GAMMALNReturns the natural logarithm of the gamma function, Γ(x)
GEOMEANReturns the geometric mean
GROWTHReturns values along an exponential trend
HARMEANReturns the harmonic mean
HYPGEOMDISTReturns the hypergeometric distribution
INTERCEPTReturns the intercept of the linear regression line
KURTReturns the kurtosis of a data set
LARGEReturns the k-th largest value in a data set
LINESTReturns the parameters of a linear trend
LOGESTReturns the parameters of an exponential trend
LOGINVReturns the inverse of the lognormal distribution
LOGNORMDISTReturns the cumulative lognormal distribution
MAXReturns the maximum value in a list of arguments
MAXAReturns the maximum value in a list of arguments, including numbers, text, and logical values
MEDIANReturns the median of the given numbers
MINReturns the minimum value in a list of arguments
MINAReturns the smallest value in a list of arguments, including numbers, text, and logical values
MODEReturns the most common value in a data set
NEGBINOMDISTReturns the negative binomial distribution
NORMDISTReturns the normal cumulative distribution
NORMINVReturns the inverse of the normal cumulative distribution
NORMSDISTReturns the standard normal cumulative distribution
NORMSINVReturns the inverse of the standard normal cumulative distribution
PEARSONReturns the Pearson product moment correlation coefficient
PERCENTILEReturns the k-th percentile of values in a range
PERCENTRANKReturns the percentage rank of a value in a data set
PERMUTReturns the number of permutations for a given number of objects
POISSONReturns the Poisson distribution
PROBReturns the probability that values in a range are between two limits
QUARTILEReturns the quartile of a data set
RANKReturns the rank of a number in a list of numbers
RSQReturns the square of the Pearson product moment correlation coefficient
SKEWReturns the skewness of a distribution
SLOPEReturns the slope of the linear regression line
SMALLReturns the k-th smallest value in a data set
STANDARDIZEReturns a normalized value
STDEVEstimates standard deviation based on a sample
STDEVAEstimates standard deviation based on a sample, including numbers, text, and logical values
STDEVPCalculates standard deviation based on the entire population
STDEVPACalculates standard deviation based on the entire population, including numbers, text, and logical values
STEYXReturns the standard error of the predicted y-value for each x in the regression
TDISTReturns the Student's t-distribution
TINVReturns the inverse of the Student's t-distribution
TRENDReturns values along a linear trend
TRIMMEANReturns the mean of the interior of a data set
TTESTReturns the probability associated with a Student's t-test
VAREstimates variance based on a sample
VARAEstimates variance based on a sample, including numbers, text, and logical values
VARPCalculates variance based on the entire population
VARPACalculates variance based on the entire population, including numbers, text, and logical values
WEIBULLReturns the Weibull distribution
ZTESTReturns the one-tailed probability-value of a z-test

Text functions

FunctionDescription
ASC Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
CHAR Returns the character specified by the code number
CLEANRemoves all nonprintable characters from text
CODEReturns a numeric code for the first character in a text string
CONCATENATEJoins several text items into one text item
DOLLARConverts a number to text, using the $ (dollar) currency format
EXACTChecks to see if two text values are identical
FIND, FINDBFinds one text value within another (case-sensitive)
FIXEDFormats a number as text with a fixed number of decimals
JISChanges half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT, LEFTBReturns the leftmost characters from a text value
LEN, LENBReturns the number of characters in a text string
LOWERConverts text to lowercase
MID, MIDBReturns a specific number of characters from a text string starting at the position you specify
PHONETICExtracts the phonetic (furigana) characters from a text string
PROPERCapitalizes the first letter in each word of a text value
REPLACE, REPLACEBReplaces characters within text
REPTRepeats text a given number of times
RIGHT, RIGHTBReturns the rightmost characters from a text value
SEARCH, SEARCHBFinds one text value within another (not case-sensitive)
TConverts its arguments to text
TEXTFormats a number and converts it to text
TRIMRemoves spaces from text
UPPERConverts text to uppercase
VALUEConverts a text argument to a number

10 comments :

  1. Not all of these excel functions work in sharepoint to my knowledge.

    Have you tried getting a random number between 0-100 using RANDBETWEEN ?

    ReplyDelete
  2. You're right, a lot of them don't work...

    random number never tried but both RAND()*100 and RANDBETWEEN(1,100) don't work.

    Only other option I can think of it a custom field... if you think of anything else let us know.

    ReplyDelete
  3. try this: http://www.codeplex.com/iconset

    ReplyDelete
  4. Some date functions don't work in SharePoint but one that DOES work and is very useful is DATEDIF to calculate the intervale betweeb two dates. That's not on the list but it works fine.

    ReplyDelete
  5. Here's a list of he functions that you can use in SharePoint calculated fields:

    http://office.microsoft.com/en-us/sharepointtechnology/CH100650061033.aspx

    ReplyDelete
  6. Lars, thanks for the link. At the time I wrote this, MS didn't have a list of available calculated field functions.

    ReplyDelete
  7. any input on how to calculate the number of words (not characters) in a list column?

    ReplyDelete
  8. You can use COUNTA(filed1,field2,field3) to calculate the number of words (not characters) in a list column.

    ReplyDelete
  9. Klara: SUBSTITUTE also doesn't work

    ReplyDelete
  10. @Klara: Thanks, I've removed the row.

    ReplyDelete