Sharepoint 2013 Evaluating Choice Column value in calculated columns

…this post was published on 2015 July 14.

Let’s consider the following scenario , started from this post on MS Forum platform

<< you need a calendar list with a column,  a numeric one : [ TrailerNumber], the second one as a choice column [ServiceType]
If value in [ServiceType]=”PM DUE” then a new column , let’s name it [EVENT_Calculated] will be equal to current item’s event date + TrailerNumber >>

and this should be done with Sharepoint Designer. Or not?

I prefer to avoid workflows , if not mandatory. So I created a new calendar list and 3 new columns:
1. ServiceType    – a Choice Column, one of these values is: “PM DUE”  –  a mandatory value is checked
2. TrailerNumber –  a Numeric Column, no decimals, value is  mandatory when a new item is added or edited.

The third column should be a calculated column, let’s name it Event_Calculated, where I will evaluate ServiceType’s value. If you try to evaluate a Choice column in a calculated column you will can not. Isn’t possible.
What is possible is a workaround: Create a new calculated column  Service_Type_Calculated, based on ServiceType Column, as a single text column and next create the final calculated column Event_Calculated

The intermediate column Service_Type_Calculated is this one

and the needed column Event_Calculated

with the formula:   =IF(Service_Type_Calculated=”PM DUE”,[Start Time]+TrailerNumber,[End Time])

This entry was posted in CalculatedColumn, Sharepoint 2013. Bookmark the permalink.