The Supported and Unsupported Columns In SharePoint Calculated Column Formula

In this article, I will list

The Supported and Unsupported Fields in SharePoint Calculated Column Formula

The Supported and Unsupported Columns in SharePoint Calculated Column Formula

The Supported fields in SharePoint Calculated Column Formula.

By default, the below columns can be used in SharePoint Calculated Column Formula:

  • Title.
  • Created.
  • Modified.

Also, the columns with the below data type are supported in SharePoint Calculated Column Formula:

  • A single line of text.
  • Choice (menu to choose from).
  • Number (1, 1.0, 100).
  • Currency ($, ¥, €).
  • Date and Time.
  • Yes/No (checkbox).
  • Calculated (calculation based on other columns).
  • Task Outcome.
  • External Data.
  • Content-Type Columns.

The Unsupported fields in SharePoint Calculated Column Formula.

[ID]

Although the SharePoint calculated column formula will accept the ID field without any error, however, it will not be calculated on item added or changed!

Note: The ID value will only be calculated when the Calculated field formula is updated again in the calculated column field settings.

Check this workaround: Using ID Field in SharePoint Calculated Column Formula.

As shown below, the ID Column is not listed in the available columns, it’s not supported!

Using ID in a Calaulated Column in SharePoint


[Today]

The [Today] is not supported in the Calculated Column. however, to get the current date you can use:

  • TODAY(), NOW() as a function that will be returned as DateTime.
  • [Modified] field.

Using Today in SharePoint Calculated Column

Note: You should be aware of the Today() value will not be preserved the first saved [Today] value! it will be recalculated again to get the current date for each Edit Item.


[ME]

The [ME] is not supported in the Calculated Column.


Multiple lines of text

Whatever it’s “Plain text” or “Enhanced rich text”, the “Multiple lines of text” field is not supported in the Calculated Column.


Lookup (information already on this site)

Whatever it’s a “Single value” or “Multiple values“, the “Lookup” field is not supported in the Calculated Column.

Check this workaround: Using Lookup Field in SharePoint Calculated Column Formula.


Person or Group

Whatever it’s a “People Only” or “People and Groups“, the “Person Or Group” field is not supported in the Calculated Column


Hyperlink or Picture

The “Hyperlink or Picture” field is not supported in the Calculated Column.


Managed Metadata

The “Managed Metadata” field is not supported in the Calculated Column.


HTML Tag

For SharePoint Online, Adding HTML tags in SharePoint calculated column has been blocked on June 13, 2017.By the way, Administrators can request an extension through September 10, 2017, at the latest. During this extension, execution of custom markup in calculated fields will not be blocked. This request can be submitted through Microsoft Support. However, beginning September 10, 2017, all unsupported markup will be ignored.

For SharePoint 2016/2013, The June 2017 PU and subsequent PUs will include a new web application setting that’s called CustomMarkupInCalculatedFieldDisabled.

This setting lets an on-premises administrator determine whether execution of custom markup in calculated fields in a given web application is blocked.

The following is the default behavior for existing and new web applications at the time of the update installation.

  • Newly created web app after the update is installed: Block execution of custom HTML markup in calculated fields.
  • Existing web app after the update is installed: Allow execution of custom HTML markup in calculated fields.

The following are some useful links to JSLink as a supported workaround:

Also, For SharePoint 2013 / 2016 On-Prem,  you can enable CustomMarkupInCalculatedFieldDisabled via PowerShell as the following


$web = Get-SPWebApplication http://WebAppURL
$web.CustomMarkupInCalculatedFieldDisabled = $false
$web.Update()

Read more at Handling HTML markup in SharePoint calculated fields.


Keep in mind

The calculated column formula is only calculated/updated in the following cases:

  1. Add New Item.
  2. Update Existing item.
  3. Update the calculated column itself in the List Setting!

Applied To
  • SharePoint Online.
  • SharePoint 2016
  • SharePoint 2013
  • SharePoint 2010
Conclusion

In this article, I have listed The Supported and Unsupported Fields in SharePoint Calculated Column Formula.

See Also
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s