Issue 58589 - summation of time fields appears broken
Summary: summation of time fields appears broken
Status: CLOSED DUPLICATE of issue 5658
Alias: None
Product: Calc
Classification: Application
Component: programming (show other issues)
Version: OOo 1.0.0
Hardware: Sun All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: frank
QA Contact: issues@sc
Depends on:
Reported: 2005-11-28 18:31 UTC by stephenwright
Modified: 2005-12-01 14:15 UTC (History)
1 user (show)

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

spreadsheet demonstarting excel date arithmetic (14.50 KB, application/
2005-11-30 21:00 UTC, stephenwright
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description stephenwright 2005-11-28 18:31:12 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.
Comment 1 stephenwright 2005-11-28 20:22:19 UTC
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.

Comment 2 kla 2005-11-30 11:10:38 UTC
Seems to be yours.
Comment 3 frank 2005-11-30 12:29:46 UTC

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.

Comment 4 stephenwright 2005-11-30 21:00:18 UTC
Created attachment 31948 [details]
spreadsheet demonstarting excel date arithmetic
Comment 5 stephenwright 2005-11-30 21:08:14 UTC
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")

Comment 6 frank 2005-12-01 11:49:37 UTC
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 .


*** This issue has been marked as a duplicate of 5658 ***
Comment 7 frank 2005-12-01 11:50:12 UTC
closed double
Comment 8 stephenwright 2005-12-01 13:57:09 UTC
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 ?
Comment 9 frank 2005-12-01 14:15:01 UTC

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 !


*** This issue has been marked as a duplicate of 5658 ***
Comment 10 frank 2005-12-01 14:15:17 UTC
closed double