Description: When opening a .xlsx file the cells (Format: [H]:MM) that contain the formula "=CELL1-CELL2" sometimes show "###" when the result is negative. When opening the file and saving to .xls or .ods the newly created file does not come with the same error. However saving the working file as .xlsx brings back the problem. Steps to Reproduce: 1.Open a certain .xlsx file. For privacy reasons only available upon request. 2.Have a look at the cells "Vormonat" and "Akt. Stand" from sheet "November17" to "März18". 3. Actual Results: Some cells (Format: [H]:MM) that contain the formula "=CELL1-CELL2" show "###" when the result is negative. Expected Results: The cells (Format: [H]:MM) that contain the formula "=CELL1-CELL2" show "###" should have shown the result even when the result is negative. Reproducible: Always User Profile Reset: Yes Additional Info: Version: 5.4.6.2 (x64) Build-ID: 4014ce260a04f1026ba855d3b8d91541c224eab8 CPU-Threads: 4; BS: Windows 6.19; UI-Render: Standard; Gebietsschema: de-DE (de_DE); Calc: group User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:59.0) Gecko/20100101 Firefox/59.0
Please check if the problem you describe can be shown using a file cleared from the privacy concerns by erasing everything unrelated? Just keep a single sheet with the two dates and the formula, with formatting, and post the result here.
Also note that, unlike ODF, OOXML format doesn't allow negative dates and times. So, the problem might be the mere format limitation.
Created attachment 140839 [details] xlsx file with negative dates
Up to this point the file worked completly fine. After the updates to 5.4.5.1 as well as 5.4.6.2 that I performed today part of this file does have the error. But maybe it's the file itself that is corrupt in some way. Honestly I don't think so due to the fact that after opening it and saving it again to .xls or .ods it works as it is supposed to. Just out of curiosity I opened it with Microsoft Office which works as well.
no repro Version: 5.4.7.0.0+ Build ID: 19f0b25acc2a23b790301755a9fee2629eb667b0 CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group Version: 6.0.4.0.0+ Build ID: 90fb652ebbc4b16ae5001140076f52209e913345 CPU threads: 8; OS: Linux 4.14; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group maybe only windows???
The file is OK; but possibly there's some slight change in text/cell width calculation that makes the "-16:05" to not fit to the cell. Just make the cell slightly wider. Generally it's impossible to achieve ideal fidelity up to single pixel, e.g. because OOXML and ODF use different length units, and thus riunding errors may lead up to two smaller units difference; so it is advisable to not try to make cells as narrow as possible, without any extra space, because it would eventually lead to such problems (with new versions of fonts/software/even OS). See http://youtu.be/Q8YiYw9TtEg?hd=1 for the screencast with Version: 6.0.3.1 (x64) Build ID: 62abb169b0efa1520d7bee1f586865354060b989 CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: ru-RU (ru_RU); Calc: CL. I will close the issue as NOTABUG. If you find an evidence of really wrong width calculations, please reopen as UNCONFIRMED with explanation and test case.
Well I was pretty confident that it's a rendering problem. Thank you for the confirmation... I as a normal user never realized that ### can also mean that the answer is simply to large for the cell. It might be misguiding for other users as well to show the exact same "error" behaviour when having an real error in your formula... If there was some sort of message telling me that my cells are to small for the result I wouldn't even be wasting the precious time of open source developers. So that might be a thing to consider in future releases. Anyway why is it that cells containing numbers become ### yet cells containing text simply get truncated? By the way a huge thanks to all of you developers creating and maintaining an incredibly advanced alternative to Microsoft Office.
The behaviour is documented, e.g., here: https://help.libreoffice.org/Calc/Error_Codes_in_Calc - the very first row. ### never means other errors: if it is shown, it always means that what is to be shown doesn't fit. It may happen that an error message doesn't fit; then - yes, there would be ### there, to show the same: show string doesn't fit. You also may select the cell and look at the status bar, where the error would be explained (if there is an error), or the cell value (or sum, or average, etc.) would be shown for normal cells (like in this case). Wrt strings - well, for a string, it's enough to show a red triangle meaning that there's something left; but for a number, any part of a number doesn't make sense by itself.
Thank you for the link to the error code documentation. I never realised that there was such a thing. Same goes for the error messages on the status bar I never knew of them. That is due to the fact that normally those error messages are displayed in the cells themselves. (It also never crossed my mind that ### itself could be an error message since it didn't contain any letters. But that might be just stupidity on my side...) May I suggest to show the error message "Cell not wide enough" on the status bar when error ### occurs.