Pages

Tuesday, 10 July 2012

Calculated columns in SharePoint

One of the cool field type is the Calculated field. That is used to do some custom calculation or performing some logic based on existing field.

I used calculated field in one of the project that was based on SharePoint workflow and had some logic to kick of workflow. It was late 2 years back and that time I was not blogging. Recently I created couple of Calculated columns so I decided to blog it so that I can and my SharePoint felows can get benefit now and in future as well.

Calculated field works like Excel cell. For instance we want to calculate the next schedule/review date based after 60 days of the date supplied.

The formula for above scenario is very simple:

Next review date: [Review Date] + 60

Similarly if you want to show "First Name" + "Last Name" as Name in the list, just use below simple formula:

Name: [First Name] + " " + [Last Name]

We can also use IF ELSE in the formula. For instance if you need to calculate schedule/review date based on type of document. If document type is Urgent, then review should happen in 10 days otherwise in 30 days.

Next review date: =IF([Document Type]="Urgent", [Review Date]+10, [Review Date]+30)
Where [Document Type] is choice field, and 'Next review date' is calculated field type.

Remember, the fiels must be closed with [ and ] brackets.

Whenever I need to use formula in Calculated field, I open my excel and create a formula and use it in SharePoint.

For further reference please visit http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx