Apache OpenOffice (AOO) Bugzilla – Issue 58589
summation of time fields appears broken
Last modified: 2005-12-01 14:15:17 UTC
This calculation works on excel, but not on open-office microsoft shows A B D 4 9:30AM 5 Agenda Introduction 5 9:35AM 25 Strategy 6 9:50 AM ...... Where the spread-sheet formula is of the form A6=A5 + C5 where the function wizard shows C5 as 00:25 and shows A6 as .4 and shows hour(a6) as 9 and shows minute(a6) as 30 open office calc shows A B D 4 9:30AM 5 Agenda Introduction 5 9:30AM 15 Strategy 6 9:30AM 15 A6=A5 + C5 All meeting time (A6->A14) are displayed with same value, 9:30 instead of 9:30 + minute duration of last meeting.
Note that the microsoft spread-sheet shows that C5 is CONCATENATE("00:",B5,":00"). In order to see the formula to calculate C5, I had to hit Trace Precedents twice, and then double click on the 2nd end-point. When I do the same under open office "calc", I am not able to ever resolve what C6 (or C7, C8 etc) should be. There is only one action under trace precedents, not two as in excel. A = time B = General C ?? D is a text field, but i cannot verify that by clicking on cell-format.
Seems to be yours.
Hi, Calc does not calculate with text, as you've told us, you use the CONCATENATE function to get the result. This function produces text and therefore it seems to me that the calculation you want to do fails. It would be fine if you can attach a document showing the problem. Thanks Frank
Created attachment 31948 [details] spreadsheet demonstarting excel date arithmetic
Calculating time in cell A from previous cell A time + previous cell B meeting, duration) works in excel, but not in Open Office. The question is, should it ? A B C ........ 4 9:30 5 5 9:35 (=A4+C4) 10 CONCATENATE("00:", B4,":00") 6 9:45 (=A5+C5) 15 CONCATENATE("00:", B5,":00")
Hi Stephen, strictly spoken. No, it should not, as text is text and not a number. To solve your problem, just use the value function as follows : =value(CONCATENATE("00:"; B4;":00")) I close this Issue as double to Issue 5658 . Frank *** This issue has been marked as a duplicate of 5658 ***
closed double
Re: Open Office's Reason For Being I thought that the whole idea behind Open Office was to provide a work-alike for the rest of the world to use. If not, then the rest of my pleadings are unfounded and you can stop reading here. But if we are looking for conformance ..... Are you saying that the way that my example sheet was coded in a style that is not in general use, and I'll not receive a sheet like that in the future ( =time-format-cell + time-formatted-text) ? After all, I was dependent on that sheet to tell me my meeting times, and I did assume that if I used Open Office that I'd see what a Microsoft user would see. I was wrong. Only the PC users got to see their meeting times. If excel casts the type of the right operand to the type of the left operand but we don't, then aren't we going to run into a multitude of compatibility problems going down the road ?
Stephen, we are not an Excel clone and will not be ! Issue 5658 deals exactly with the Issue described by you, so it's clearly a duplicate ! Frank *** This issue has been marked as a duplicate of 5658 ***