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: Calculated Columns
Was banging my head on this one. I knew the answer had to be simple, but couldn’t find it. Thanks to your post, now I can stop banging my head. Thanks!
Hi Ryan..
I´m using SharePoint Online to validate a Yes/No field using exact the same format as you recommend but SharePoint always indicate that the formula has an error or is not supported.
Thanks in advance for your help!
Hi,
I have a series of records each with 3 currency values (1, 2 & 3) and a yes/no field
I have a total invoice value text box in the footer which adds all the invoice total values – works fine. I have another totals text box in the footer to calculate invoices paid total but it seems to ignore the true/false field and just gives the same total
The code in the ControlSource is:
=IIf([InvoiceStatus]=True,Sum(nz([InvoiceAmount1]))+Sum(Nz([InvoiceAmount2]))+Sum(nz([InvoiceAmount3])),0)
I also tried the reverse (false) and 0/1 – also not working
Anyone have any suggestions?
Thank you
When the user loads the form, the comments box should be hidden. If the user selects the “Yes” option, the comments box should remain hidden. When the user selects the “No” button, the comments box should appear and be mandatory, but if the user then selects “Yes” again, the box should disappear and no longer be mandatory.
Excelente, me ayudo mucho tus consejos.
Saludos