Issue 86708

Summary:

division of cells in time format

Product:

Calc

Reporter:

brendel <brendel>

Component:

code

Assignee:

spreadsheet <spreadsheet>

Status:

CLOSED NOT_AN_OOO_ISSUE

QA Contact:

issues@sc <issues>

Severity:

Trivial

Priority:

CC:

issues

Version:

OOo 2.3.1

Target Milestone:

---

Hardware:

All

OS:

All

Issue Type:

DEFECT

Latest Confirmation in:

---

Developer Difficulty:

---

Attachments:

Description	Flags
in a cell a time filed is divided by 2, which gives "0"	none

Description brendel 2008-03-05 09:05:53 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

Comment 1 brendel 2008-03-05 09:07:07 UTC

Created attachment 51892 [details]
in a cell a time filed is divided by 2, which gives "0"

Comment 2 frank 2008-03-05 15:09:38 UTC

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

Comment 3 frank 2008-03-05 15:10:01 UTC

closed invalid

Comment 4 brendel 2008-03-05 15:29:08 UTC

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

Comment 5 ooo 2008-03-05 15:31:48 UTC

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))

Comment 6 ooo 2008-03-05 18:48:36 UTC

> 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.