SharePoint Calculated Fields Use Excel Formulas.
转自：http://blog.ray1.net/2008/04/sharepoint-calculated-fields-use-excel.html#Information%20functions
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)
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
| Function | Description |
|---|---|
| DATE | Returns the serial number of a particular date |
| DATEVALUE | Converts a date in the form of text to a serial number |
| DAY | Converts a serial number to a day of the month |
| DAYS360 | Calculates the number of days between two dates based on a 360-day year |
| EDATE | Returns the serial number of the date that is the indicated number of months before or after the start date |
| EOMONTH | Returns the serial number of the last day of the month before or after a specified number of months |
| HOUR | Converts a serial number to an hour |
| MINUTE | Converts a serial number to a minute |
| MONTH | Converts a serial number to a month |
| NETWORKDAYS | Returns the number of whole workdays between two dates |
| NOW | Returns the serial number of the current date and time |
| SECOND | Converts a serial number to a second |
| TIME | Returns the serial number of a particular time |
| TIMEVALUE | Converts a time in the form of text to a serial number |
| TODAY | Returns the serial number of today's date |
| WEEKDAY | Converts a serial number to a day of the week |
| WEEKNUM | Converts a serial number to a number representing where the week falls numerically with a year |
| WORKDAY | Returns the serial number of the date before or after a specified number of workdays |
| YEAR | Converts a serial number to a year |
| YEARFRAC | Returns the year fraction representing the number of whole days between start_date and end_date |
Information functions
| Function | Description |
|---|---|
| CELL | Returns information about the formatting, location, or contents of a cell |
| ERROR.TYPE | Returns a number corresponding to an error type |
| INFO | Returns information about the current operating environment |
| ISBLANK | Returns TRUE if the value is blank |
| ISERR | Returns TRUE if the value is any error value except #N/A |
| ISERROR | Returns TRUE if the value is any error value |
| ISEVEN | Returns TRUE if the number is even |
| ISLOGICAL | Returns TRUE if the value is a logical value |
| ISNA | Returns TRUE if the value is the #N/A error value |
| ISNONTEXT | Returns TRUE if the value is not text |
| ISNUMBER | Returns TRUE if the value is a number |
| ISODD | Returns TRUE if the number is odd |
| ISREF | Returns TRUE if the value is a reference |
| ISTEXT | Returns TRUE if the value is text |
| N | Returns a value converted to a number |
| NA | Returns the error value #N/A |
| TYPE | Returns a number indicating the data type of a value |
Logical functions
| Function | Description |
|---|---|
| AND | Returns TRUE if all of its arguments are TRUE |
| FALSE | Returns the logical value FALSE |
| IF | Specifies a logical test to perform |
| IFERROR | Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula |
| NOT | Reverses the logic of its argument |
| OR | Returns TRUE if any argument is TRUE |
| TRUE | Returns the logical value TRUE |
Math and trigonometry functions
| Function | Description |
|---|---|
| ABS | Returns the absolute value of a number |
| ACOS | Returns the arccosine of a number |
| ACOSH | Returns the inverse hyperbolic cosine of a number |
| ASIN | Returns the arcsine of a number |
| ASINH | Returns the inverse hyperbolic sine of a number |
| ATAN | Returns the arctangent of a number |
| ATAN2 | Returns the arctangent from x- and y-coordinates |
| ATANH | Returns the inverse hyperbolic tangent of a number |
| CEILING | Rounds a number to the nearest integer or to the nearest multiple of significance |
| COMBIN | Returns the number of combinations for a given number of objects |
| COS | Returns the cosine of a number |
| COSH | Returns the hyperbolic cosine of a number |
| DEGREES | Converts radians to degrees |
| EVEN | Rounds a number up to the nearest even integer |
| EXP | Returns e raised to the power of a given number |
| FACT | Returns the factorial of a number |
| FACTDOUBLE | Returns the double factorial of a number |
| FLOOR | Rounds a number down, toward zero |
| GCD | Returns the greatest common divisor |
| INT | Rounds a number down to the nearest integer |
| LCM | Returns the least common multiple |
| LN | Returns the natural logarithm of a number |
| LOG | Returns the logarithm of a number to a specified base |
| LOG10 | Returns the base-10 logarithm of a number |
| MDETERM | Returns the matrix determinant of an array |
| MINVERSE | Returns the matrix inverse of an array |
| MMULT | Returns the matrix product of two arrays |
| MOD | Returns the remainder from division |
| MROUND | Returns a number rounded to the desired multiple |
| MULTINOMIAL | Returns the multinomial of a set of numbers |
| ODD | Rounds a number up to the nearest odd integer |
| PI | Returns the value of pi |
| POWER | Returns the result of a number raised to a power |
| PRODUCT | Multiplies its arguments |
| QUOTIENT | Returns the integer portion of a division |
| RADIANS | Converts degrees to radians |
| ROMAN | Converts an arabic numeral to roman, as text |
| ROUND | Rounds a number to a specified number of digits |
| ROUNDDOWN | Rounds a number down, toward zero |
| ROUNDUP | Rounds a number up, away from zero |
| SERIESSUM | Returns the sum of a power series based on the formula |
| SIGN | Returns the sign of a number |
| SIN | Returns the sine of the given angle |
| SINH | Returns the hyperbolic sine of a number |
| SQRT | Returns a positive square root |
| SQRTPI | Returns the square root of (number * pi) |
| SUBTOTAL | Returns a subtotal in a list or database |
| SUM | Adds its arguments |
| SUMIF | Adds the cells specified by a given criteria |
| SUMIFS | Adds the cells in a range that meet multiple criteria |
| SUMPRODUCT | Returns the sum of the products of corresponding array components |
| SUMSQ | Returns the sum of the squares of the arguments |
| SUMX2MY2 | Returns the sum of the difference of squares of corresponding values in two arrays |
| SUMX2PY2 | Returns the sum of the sum of squares of corresponding values in two arrays |
| SUMXMY2 | Returns the sum of squares of differences of corresponding values in two arrays |
| TAN | Returns the tangent of a number |
| TANH | Returns the hyperbolic tangent of a number |
| TRUNC | Truncates a number to an integer |
Statistical functions
| Function | Description |
|---|---|
| AVEDEV | Returns the average of the absolute deviations of data points from their mean |
| AVERAGE | Returns the average of its arguments |
| AVERAGEA | Returns the average of its arguments, including numbers, text, and logical values |
| AVERAGEIF | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria |
| AVERAGEIFS | Returns the average (arithmetic mean) of all cells that meet multiple criteria. |
| BETADIST | Returns the beta cumulative distribution function |
| BETAINV | Returns the inverse of the cumulative distribution function for a specified beta distribution |
| BINOMDIST | Returns the individual term binomial distribution probability |
| CHIDIST | Returns the one-tailed probability of the chi-squared distribution |
| CHIINV | Returns the inverse of the one-tailed probability of the chi-squared distribution |
| CHITEST | Returns the test for independence |
| CONFIDENCE | Returns the confidence interval for a population mean |
| CORREL | Returns the correlation coefficient between two data sets |
| COUNT | Counts how many numbers are in the list of arguments |
| COUNTA | Counts how many values are in the list of arguments |
| COUNTBLANK | Counts the number of blank cells within a range |
| COUNTIF | Counts the number of cells within a range that meet the given criteria |
| COUNTIFS | Counts the number of cells within a range that meet multiple criteria |
| COVAR | Returns covariance, the average of the products of paired deviations |
| CRITBINOM | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
| DEVSQ | Returns the sum of squares of deviations |
| EXPONDIST | Returns the exponential distribution |
| FDIST | Returns the F probability distribution |
| FINV | Returns the inverse of the F probability distribution |
| FISHER | Returns the Fisher transformation |
| FISHERINV | Returns the inverse of the Fisher transformation |
| FORECAST | Returns a value along a linear trend |
| FREQUENCY | Returns a frequency distribution as a vertical array |
| FTEST | Returns the result of an F-test |
| GAMMADIST | Returns the gamma distribution |
| GAMMAINV | Returns the inverse of the gamma cumulative distribution |
| GAMMALN | Returns the natural logarithm of the gamma function, Γ(x) |
| GEOMEAN | Returns the geometric mean |
| GROWTH | Returns values along an exponential trend |
| HARMEAN | Returns the harmonic mean |
| HYPGEOMDIST | Returns the hypergeometric distribution |
| INTERCEPT | Returns the intercept of the linear regression line |
| KURT | Returns the kurtosis of a data set |
| LARGE | Returns the k-th largest value in a data set |
| LINEST | Returns the parameters of a linear trend |
| LOGEST | Returns the parameters of an exponential trend |
| LOGINV | Returns the inverse of the lognormal distribution |
| LOGNORMDIST | Returns the cumulative lognormal distribution |
| MAX | Returns the maximum value in a list of arguments |
| MAXA | Returns the maximum value in a list of arguments, including numbers, text, and logical values |
| MEDIAN | Returns the median of the given numbers |
| MIN | Returns the minimum value in a list of arguments |
| MINA | Returns the smallest value in a list of arguments, including numbers, text, and logical values |
| MODE | Returns the most common value in a data set |
| NEGBINOMDIST | Returns the negative binomial distribution |
| NORMDIST | Returns the normal cumulative distribution |
| NORMINV | Returns the inverse of the normal cumulative distribution |
| NORMSDIST | Returns the standard normal cumulative distribution |
| NORMSINV | Returns the inverse of the standard normal cumulative distribution |
| PEARSON | Returns the Pearson product moment correlation coefficient |
| PERCENTILE | Returns the k-th percentile of values in a range |
| PERCENTRANK | Returns the percentage rank of a value in a data set |
| PERMUT | Returns the number of permutations for a given number of objects |
| POISSON | Returns the Poisson distribution |
| PROB | Returns the probability that values in a range are between two limits |
| QUARTILE | Returns the quartile of a data set |
| RANK | Returns the rank of a number in a list of numbers |
| RSQ | Returns the square of the Pearson product moment correlation coefficient |
| SKEW | Returns the skewness of a distribution |
| SLOPE | Returns the slope of the linear regression line |
| SMALL | Returns the k-th smallest value in a data set |
| STANDARDIZE | Returns a normalized value |
| STDEV | Estimates standard deviation based on a sample |
| STDEVA | Estimates standard deviation based on a sample, including numbers, text, and logical values |
| STDEVP | Calculates standard deviation based on the entire population |
| STDEVPA | Calculates standard deviation based on the entire population, including numbers, text, and logical values |
| STEYX | Returns the standard error of the predicted y-value for each x in the regression |
| TDIST | Returns the Student's t-distribution |
| TINV | Returns the inverse of the Student's t-distribution |
| TREND | Returns values along a linear trend |
| TRIMMEAN | Returns the mean of the interior of a data set |
| TTEST | Returns the probability associated with a Student's t-test |
| VAR | Estimates variance based on a sample |
| VARA | Estimates variance based on a sample, including numbers, text, and logical values |
| VARP | Calculates variance based on the entire population |
| VARPA | Calculates variance based on the entire population, including numbers, text, and logical values |
| WEIBULL | Returns the Weibull distribution |
| ZTEST | Returns the one-tailed probability-value of a z-test |
Text functions
| Function | Description |
|---|---|
| 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 |
| CLEAN | Removes all nonprintable characters from text |
| CODE | Returns a numeric code for the first character in a text string |
| CONCATENATE | Joins several text items into one text item |
| DOLLAR | Converts a number to text, using the $ (dollar) currency format |
| EXACT | Checks to see if two text values are identical |
| FIND, FINDB | Finds one text value within another (case-sensitive) |
| FIXED | Formats a number as text with a fixed number of decimals |
| JIS | Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters |
| LEFT, LEFTB | Returns the leftmost characters from a text value |
| LEN, LENB | Returns the number of characters in a text string |
| LOWER | Converts text to lowercase |
| MID, MIDB | Returns a specific number of characters from a text string starting at the position you specify |
| PHONETIC | Extracts the phonetic (furigana) characters from a text string |
| PROPER | Capitalizes the first letter in each word of a text value |
| REPLACE, REPLACEB | Replaces characters within text |
| REPT | Repeats text a given number of times |
| RIGHT, RIGHTB | Returns the rightmost characters from a text value |
| SEARCH, SEARCHB | Finds one text value within another (not case-sensitive) |
| T | Converts its arguments to text |
| TEXT | Formats a number and converts it to text |
