Description: ISLOGICAL function does not always return TRUE when the column is not set to precisely BOOLEAN For instance, Setting a Custom format of T;;F will always return false. Also, ISLOGICAL does not change after the column format changes until the document is reloaded or the formula in the target cell is modified. Steps to Reproduce: 1. Create spreadsheet in LO 6 2. Set column A to custom format T;;F 3. Set Column B to Logical 3a. Set Column C to Number/Generic 4. Set the value =TRUE() in A1, B1 and C1 5. Set the value =FALSE() in A2, B2, and C2 6. Reference 6 cells with ISLOGICAL to the cells A1, B1, C1, A2, B2, C2. 7. Note items in column A are considered "False" 8. Note items in column B are considered "True" (Truthy?) 9. Note items in column C are considered "False" Actual Results: Functions with custom T/F header are always considered FALSE() by ISLOGICAL(). ISLOGICAL should evaluate the function and not the header. Expected Results: ISLOGICAL should evaluate the function in the cell, not the cell format (okay, a TEXT cell I *might* understand) Reproducible: Always User Profile Reset: Yes Additional Info: XLS/XLSX bug! When saving a spreadsheet to XLS/XLSX format BOOLEAN is converted to TRUE;TRUE;FALSE, and all conditional formatting the references that column & ISLOGICAL are broken. (Assuming that the CF is not stripped away altogether). Oldest version that this still applies to v4.4.7.2 User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:58.0) Gecko/20100101 Firefox/58.0
Created attachment 139507 [details] ISLOGICAL before XLS (with Boolean formatting)
Created attachment 139508 [details] ISLOGICAL after XLS (boolean formatting lost completely)
Ok, the example file does not match the description, but anyway: column D has this formatting code: [>0]"T";[<0]"T";"F" ISLOGICAL seems to think it is broken and thus says FALSE for all the column D values. I want to check with Eike before doing anything.
It is like it is. Calc does not have a distinct logical data type, but formatting as BOOLEAN displays 0 as FALSE and any other value as TRUE. Asking a cell for ISLOGICAL() therefore has to query the cell format whether it is bool. Excel has a distinct logical type, but does not (or does it meanwhile?) handle the ==0=>FALSE, <>0=>TRUE behaviour with a boolean format, so we have to export a "TRUE";"TRUE";"FALSE" number format for that. That again is not a boolean format but just a specific format with implicit >0;<0;=0 conditions. We maybe could recognize that very format during import and convert to boolean for ISLOGICAL() to work as expected, but note that ISLOGICAL() in Excel with an exported numeric value doesn't work either. However, converting this specific number format to a true BOOLEAN format during import might be expected. [>0]"T";[<0]"T";"F" is definitely a numeric user defined format and will never work in the way that ISLOGICAL() would recognize it.
Bug Clarification: When I save an XLS or XLSX spreadsheet with a Logical field, when I later reload it, it loses the logical designation and becomes that [>0]"T";[<0]"T";"F" format. Clarification - on that 4th column, I changed the display format of the Boolean field to T;;F. When LibreOffice opens the file later, the boolean field has become numeric, and the column becomes [>0]"T";[<0]"T";"F". Should assigning a custom display change the column type on reload? For the ISLOGICAL test, is LibreOffice remembering (caching?) that the column is Boolean until the spreadsheet is reloaded? My apologies if I am explaining this incorrectly.
(In reply to C Hemingway from comment #5) > When I save an XLS or XLSX spreadsheet with a Logical field, when I later > reload it, it loses the logical designation and becomes that > [>0]"T";[<0]"T";"F" format. There is no boolean logical type for D2:D3, even in the file format the cell type is office:value-type="float" calcext:value-type="float". That is because the [>0]"T";[<0]"T";"F" format is of type number and not boolean. > Clarification - on that 4th column, I changed the display format of the > Boolean field to T;;F. What do you expect? T;;F works only by accident (if T and F don't have some other meaning as format keywords) and then is the same as "T";;"F" Which when re-loaded becomes [>0]"T";[<0]"";"F" that is identical in meaning. Note the second subformat has an empty string, not "T" as your other format. I don't know how you got to the [>0]"T";[<0]"T";"F" > When LibreOffice opens the file later, the boolean > field has become numeric There never was a boolean "field" if you applied the T;;F format. > and the column becomes [>0]"T";[<0]"T";"F". > Should assigning a custom display change the column type on reload? There is no column type, there are cell formats which lead to numeric/date/boolean display cell types. > For the > ISLOGICAL test, is LibreOffice remembering (caching?) that the column is > Boolean until the spreadsheet is reloaded? No, as explained previously already, there is no distinct boolean type, it is all about number display formats. Btw, there's an odd user-defined number format in that document which loads as [>0]"T"[~jewish]YYYY"U"YY;[<0]"T"[~jewish]YYYY"U"YY;"FAL"S[~jewish]YY listed as T5663U663 and currently displays a FAL0660 preview on an empty cell or 0. Seems to be some result of your previous experiments, but it illustrates how almost any combination of valid keywords yields some result ;-)
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ce4fc2fc08be8ea2773194e303ed42d2579e93a0 Resolves: tdf#115351 convert boolean equivalent format codes to proper Boolean It will be available in 6.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/50645 for 6-0
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=feb3da3dc53c9b702e39ec12b037b07a67bedcbd&h=libreoffice-6-0 Resolves: tdf#115351 convert boolean equivalent format codes to proper Boolean It will be available in 6.0.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.