1

This MSDN article lists various functions that can be used in SharePoint calculated columns, and also includes the following disclaimer:

Note

Microsoft SharePoint Foundation formulas for calculated fields are based on Microsoft Excel functions and syntax. However, Microsoft supports only those functions mentioned on this page for use in SharePoint Foundation calculated fields. For example, the Excel function MID is not supported.

That article is for SharePoint Foundation (the free version). Microsoft used to have a similar article for SharePoint Server, but I can't find it - maybe they deleted it. (SharePoint Server does support MID, by the way.)

Regardless, there are certainly numerous other similar articles that list Excel functions you can use in SharePoint, but does there exist a list of those functions you cannot use?

(I realize that the number of supported functions is probably a small percentage of the complete list of Excel functions. So I'm not looking for a complete list of every unusable Excel function in SharePoint; just those that are likely to be needed in the same contexts as others that are supported. For example, SUBSTITUTE (searches a string for all occurrences of one substring and replace them with another substring) is not supported, but REPLACE (replaces whatever character is at a specific position in a string with another character) is.

In other words, what are the functions that you can't use in SharePoint, that a person used to writing Excel functions would most likely be surprised to learn do not work?

Dan Henderson
  • 781
  • 7
  • 22
  • For instance, VLOOKUP would not be an example of this, because calculated columns can only evaluate against other columns in the same item, not other items in the list. – Dan Henderson Aug 06 '15 at 22:11

1 Answers1

1

I have not kept record of the functions that did not work.

I did however try most Excel functions and compiled a list that do work.

https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

What people struggle most with is: How to use Today and Me in Calculated column

Because Today() does work in SharePoint but does not behave the same as in Excel.

CalcMaster

It is a PITA to debug Calculated Columns. Because you don't get feedback until you save a Formula and you end up having to click multiple times to get back to your Formula.

I have written a small 'CalcMaster' bookmarklet which hooks into the formula-editor and does a save of the Formula on every keypress; giving immediate feedback.
Published a first version on GitHub:

https://github.com/Danny-Engelman/CalcMaster

You could adapt this code to automatically try a list of functions

Danny '365CSI' Engelman
  • 21,176
  • 7
  • 35
  • 79