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
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 :-
So what does work?
So what you can use instead is (note NO quotes!)
The opposite is of course
=IF([Important]=FALSE,"Not a VIP","A VIP")
You can also use this as a shorthand for =TRUE
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: Calculated Columns