If you read my previous blog, you read about using calculated columns in SharePoint lists. I promised I’d look more closely at the functions that could be used in calculated column expressions. As you will recall, the functions that you can use in those expressions are the same functions (or nearly all of them) that you will find in Excel.
So suppose you want to have a column in a SharePoint list that takes the value in one column, say First Name, and combines it with the value in another column, say Last Name. Well, if you are a developer you would recognize that as a concatenation operation.
If you are not a developer you might search Excel help for "combining columns" or perhaps you would search the web for "combining columns Excel". Either one would return results that discussed concatenation. It turns out that Excel provides two ways to concatenate strings of characters. There is the CONCATENATE function which combines only two strings. There is also the concatenate operator, &. (The + sign, symbolizing addition, is also known as an operator). Like the + sign, which can be used like this: 5+4+6 to add more than two numbers, the & operator can be used to concatenate more than two strings: [First Name] & " " & [Last Name]. Look closely at the screen capture below of a SharePoint list in datasheet view. Notice the difference in the results of the CONCATENATE function and the & operator. I have also included in this graphic a function which finds the starting position of one string of characters within another string.
If you are familiar with financial calculations, then Excel’s present value function, PV, is probably familiar to you. If you are a mathematician or a scientist you might be familiar with Excel’s cosine function, COS. (Sorry to bring up bad memories of high school trig if you aren’t a mathematician, engineer or scientist.) The image below shows calculated columns in a SharePoint list using these functions in their expressions. Here too I have included another function, the IF function which here is used to compare two dates.
As I mentioned, not all of the Excel functions are supported in SharePoint. NETWORKDAYS, a function that returns the total number of business days between two dates, is not supported. However, many of the functions that are not supported are newer Excel funtions that have been added over the years to eliminate the need for long, complicated expressions that exceed Excel’s maximum number of characters in a cell. The screen shot below shows a portion of an expression that uses a number of more basic Excel functions to produce the same result as NETWORKDAYS. This is the exact expression that used to be required in Excel before the NETWORKDAYS function was introduced a few versions back. This lengthy expression will work correctly as an expression in a SharePoint calculated column to provide the number of business days between two dates.
So, make use of those Excel functions in your SharePoint calculated columns. You can do much more than you were probably aware of if you do.
Thanks to Travis Nielsen who was the inspiration behind this and my previous blog and who found the expression you see a portion of in this last image.
