Apache OpenOffice (AOO) Bugzilla – Issue 86708
division of cells in time format
Last modified: 2008-03-05 18:48:36 UTC
I jsut got an official document in xls format where some fo the calculations did not seem to work. I finally could narrow it down to a division of a cell in time format (H:MM). this seems to give the value "0" in OO, however this works in MS EXCEL (see attached file : on sheet "janv", in cell L36 the time value given in cell G27 is taken and divided by 2 (for info : G27 represents daily working hours and in L36 half days are needed. Importing directly the xls file does thus result in a non-functionning of this spreadsheet Oliver
Created attachment 51892 [details] in a cell a time filed is divided by 2, which gives "0"
Have a look at Sheet Récapitulatif 2008 Cell G30. The IF Statement makes the cell output a text and Calc will not calculate with text. You have two options, remove the double quotes from the IF Statement or set G27 on sheet janv to show =VALUE($'Récapitulatif 2008'.G30) Frank
closed invalid
Ok, thanks for the hint, but that's not really the problem : the problem is that this works under EXCEL, and the result is that I get regularly official spreadsheets with this problem, and not only once in each sheet, but x times. If I will have to edit manually all of these cells, and then assure that this will also still work in EXCEL before I send the filled in sheets back, It will be not very practical to use OO calc. Is there not a possibility to let Oo check the format of the cell when there is an "IF" statement involved and if the format is explicitely set to a number format, then not convert to a text format ? Well, I guess this will cause problems elsewhere and I will just add this to my long list of problems I have when using calc for xls files which I know will probably never be solved thanks again for your answer Oliver
Just a note: removing double quotes wouldn't work, and using VALUE() on the result might not be portable across locales. The correct approach would be to use the TIME() function, e.g. =IF($'Récapitulatif 2008'.C30="a";TIME(7;10;0);TIME(7;36;0))
> the problem is that this works under EXCEL Correct. The problem is that this works in Excel. Respectively sometimes it does, and sometimes it gives unexpected results, depending on the locale. For details see http://qa.openoffice.org/issues/show_bug.cgi?id=5658#desc110 and following. > Is there not a possibility to let Oo check the format of the cell when > there is an "IF" statement involved and if the format is explicitely > set to a number format, then not convert to a text format ? There is no conversion to text involved. The result of the formula involving IF() _is_ text. That is what the formula is told to return.