RSS Feed Is this your first time here? Get regular updates

Using Calculated Columns with Yes/No (Boolean) fields in SharePoint

Date:June 15th, 2015 Author: Tags:
Category: Calculated Columns Comments:0 ;

A fairly common SharePoint question is how to deal with the Yes/No field type (technically known as Boolean fields) when using Calculated Columns.

Typically you may want to do something like change Yes/No to something else for display purposes – for example if the fields is for Important you could put “VIP” or blank in a View instead.

What does NOT work?

If we assume the Yes/No field is called “Important” then here are some of the formula that WILL NOT work

=IF([Important]="True","VIP","")

The reason is that you’re working with a Boolean field under the hood so TRUE is a special key word and TRUE is not the same as “TRUE” which is a string with the letters T, R, U and E in.

(I show my wife examples like this when she wants to know why computer programmers are so pedantic!)

These also fail :-

=IF([Important]="Yes","VIP","")

=IF([Important]=1,"VIP","")

So what does work?

So what you can use instead is (note NO quotes!)

=IF([Important]=TRUE,"VIP","")

The opposite is of course

=IF([Important]=FALSE,"Not a VIP","A VIP")

You can also use this as a shorthand for =TRUE

=IF([Important],"VIP","")

For more tips on calculated columns be sure to check out our handy Calculated Column Cheat Sheet and other blog posts about SharePoint calculated columns and formulas.

Tip – for View Fitlers still use Yes or No (no quotes) – this is just for calculated column formula.

Tags:

Did you enjoy this post? Get the next one by RSS or by email

Leave a Reply

Anti-Spam Quiz: