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 Columns in SharePoint Calculated Column Formula.

By the default, the following columns can be used in SharePoint Calculated Column Formula.

  • Title.
  • Created.
  • Modified.

Also, the columns with the following data type are supported to use 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.

The Unsupported Columns in SharePoint Calculated Column Formula.

  • ID.
    • ID Column is not supported in SharePoint Calculated Column Formula.

Using ID in a Calaulated Column in SharePoint

Read more at Using ID Field in SharePoint Calculated Column Formula.

  • [Today].
    • You can use TODAY(), NOW() as a function returned as DateTime

Using Today in SharePoint Calculated Column

    • You can also use  [Modified] field in the calculated column to get the current date.

You should also be aware of the Today() value will be recalculated again for each new item edit based on the current date. it would not preserve the first saved [Today] value!

  • [ME].
  • Multiple lines of text.
    • Plain text.
    • Enhanced rich text.
  • Lookup (information already on this site).
    • Single value.
    • Multiple values.

Read more at Using Lookup Field in SharePoint Calculated Column Formula.

  • Person or Group.
    • People Only.
    • People and Groups.
  • Hyperlink or Picture.
  • Managed Metadata.
  • 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.


Note: 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