Apache OpenOffice (AOO) Bugzilla – Issue 40513
Division of a time variable not working
Last modified: 2005-01-13 12:14:24 UTC
After importing an EXCEL spreadsheet the following situation can arise : when in the spreadsheet a time is filled into a cell by using a condition (e.g. in cell B1 =IF(A1="a";"7:10";"7:36") ; in cell A1 there is either "A" or "B" ) and then this time is divided by a number in a different cell (e.g. =($B$1/2) ) then this is not working ( the result cell contains 00:00:00 if cell is formated as time [HH]:MM:SS; see attached file). I stumbled over this in a administrative EXCEL spreadsheet, in EXCEL this is working and it seems that the import can not convert this If the cell B1 is not automatically filled but contains "7:10" as time, then the division is working; I will try to attatche the file to the issue. If this is not working please feel free to contact me
Created attachment 21418 [details] example EXCEL file for division of a time cell
Hi, this is not a bug of OOo. The 'Timevalue' you used is a text and Calc doesn't calculate with textvalues. If you format the cell B1 in your example as time and remove the quotation marks from the 'then' and 'else' statement of the 'if' function, it works as expected. So no bug because Calc is more strict in following rules of calculation. Frank
closed invalid
I agree with you, this is not a bug of Calc, however this is a bug in the import of an EXCEL file. SO its the job of Import to do the changes you suggest below, taking away the quotation marks when the targed cell is formated as time. The kind of problem I presented is often found in administrative EXCEL spreadsheet (for example in an time spreadsheet of the European Union Administration). If these kind of operations can not be imported this will pose a major problem for administrations to change to OO
Hi, closing again as the import could not know that this kind of text should be converted to real time values. Please have a look at Issue 5658 . For now I close this as duplicate. Frank *** This issue has been marked as a duplicate of 5658 ***
closed double
Import could deduce from the cell format the format of the text. In the case here the cell is formated as time, so in my opinion it makes more sense to format the text in the if statement as time and thus to delete the quotation marks than to format it as text.... Oliver