Change Boolean to Yes/No instead of True/False

Hi

How can Calc interpret or format cells that have “Yes” or “No” word strings to 1 or 0 number values?

(Really using Spanish Si and No.)

Use as number format

“Yes”;;“No”

Result is “Yes” for positive values, “No” for zeroes, nothing for negatives.

Updated

Maybe in your language the separator is the comma not the semicolon.

Please see the attached file: YesNoFormat.ods

Sorry, first time formatting a user-defined cell.
What do I type under “Format code”?
Typed “Yes”;;“No” but nothing happens.

Please attach minimal sample file about what you want achieve. If you like there is a site in Spain https://ask.libreoffice.org/c/spanish/10

The format code separator is always ; semicolon, language independent.

i suggest basically the same as m.a.riosv wrote, but a little better:

in format cells dialogue fill format code input with the following:

[=1]"Yes";[=0]"No";General - this means it will be yes on true/1 values, no on false/0 values and will use general format otherwise.

one may even use the following string to format it as / instead of yes/no and color differentiation:
[GREEN][=1]"✓";[RED][=0]"✗";[BLUE]General;[BLUE]@

image description

this works on libreoffice calc v6.1.5.2 for ubuntu - i’ve tested

i call it “a little bit better” solution because it implements design by contract pattern, i.e. if for some unexpected reason the value will be something else than a boolean, then it will be displayed as usual, and not hidden, thus hiding a possible error in design!

Caveat, negative values are displayed without minus sign then (which may be neglect-able though but confusing). And no, if negative values are desired AFAIK there’s no format code that would cover all possibilities as there are only 3 subformats (plus the text subformat).

It occurred to me that the question was not about formatting but actually literally about interpreting a Yes or No word, or Si and No. So that would be the formula

=IF( A1="Si"; 1; IF( A1="No"; 0; A1))

assuming content in cell A1, which if that is Si displays 1, if No displays 0, else the content of A1, or 0 if A1 is empty.