Issue 40513 - Division of a time variable not working
Summary: Division of a time variable not working
Status: CLOSED DUPLICATE of issue 5658
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 1.1.4
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Depends on:
Reported: 2005-01-12 17:03 UTC by brendel
Modified: 2005-01-13 12:14 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

example EXCEL file for division of a time cell (13.50 KB, application/
2005-01-12 17:05 UTC, brendel
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description brendel 2005-01-12 17:03:58 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
Comment 1 brendel 2005-01-12 17:05:08 UTC
Created attachment 21418 [details]
example EXCEL file for division of a time cell
Comment 2 frank 2005-01-13 09:56:47 UTC

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.

Comment 3 frank 2005-01-13 09:57:05 UTC
closed invalid
Comment 4 brendel 2005-01-13 10:14:25 UTC
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
Comment 5 frank 2005-01-13 11:44:49 UTC

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.


*** This issue has been marked as a duplicate of 5658 ***
Comment 6 frank 2005-01-13 11:45:07 UTC
closed double
Comment 7 brendel 2005-01-13 12:14:24 UTC
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....