In this article, I will list
The Supported and Unsupported Fields 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.
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 Column is not supported in SharePoint Calculated Column Formula.
- You can use TODAY(), NOW() as a function returned as DateTime
- 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!
- Multiple lines of text.
- Plain text.
- Enhanced rich text.
- Lookup (information already on this site).
- Single value.
- Multiple values.
- 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:
- Client-side rendering (JS Link) code samples
- JSLink Client Side Rendering (CSR) Samples
- Client Side Rendering
- Custom field type in the SharePoint add-in model
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()
Note: The calculated column formula is only calculated/updated in the following cases:
- Add New Item.
- Update Existing item.
- Update the calculated column itself in the List Setting!
- SharePoint Online.
- SharePoint 2016
- SharePoint 2013
- SharePoint 2010
In this article, I have listed The Supported and Unsupported Fields in SharePoint Calculated Column Formula.
- Using ID Field in SharePoint Calculated Column Formula.
- Using Lookup Field in SharePoint Calculated Column Formula.