Issue 42655 - Time values entered in a formula are converted by OOoCalc to decimal numbers.
Summary: Time values entered in a formula are converted by OOoCalc to decimal numbers.
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 1.1.4
Hardware: PC Linux, all
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
Depends on:
Reported: 2005-02-13 00:49 UTC by docpi
Modified: 2013-08-07 15:12 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description docpi 2005-02-13 00:49:49 UTC
This one is simple, easy and clear-cut:

0) Enter "0:30" in a field in OOoCalc and the field correctly retains the value
in its originally entered format, "00:30:00".


1) Enter "=0:30+0:40" in another field in OOoCalc and the value stored for that
field is wrongly converted to "=0,0208333333333333+0,0277777777777778".

This behavior of OOoCalc wrognly changing the stored value of a field to one of
its possible representations, losing original format and inviting offset and
rounding errors, has popped up around 1.0.x and has not been really squashed since.
This is to say that it appears to be a problem in the fundamental cell handling
routines and that any attempts so to get to it have so far not been satisfactory.
Comment 1 frank 2005-02-14 11:16:22 UTC

this is not a bug.

The decimals displayed are used to calculate the sum. The number 1 represents 24
hours. All spreadsheets calculate with these numbers. Entering your formula into
Excel will result in an Errormessage as Excel can't calculate with these values,
they do not know that this is a time. So converting these inputs to decimals is
even better as Excel and necessary.

Closing wontfix.

Comment 2 frank 2005-02-14 11:16:36 UTC
closed wontfix
Comment 3 docpi 2005-02-14 12:37:48 UTC
[factual reply
and suggestion/request for upgrading from CLOSED-WONTFIX to at least FEATURE

Is that so? Why then is a single time value which is not embedded into a formula
correctly preserved as a time value within the cell? And you can still work with
it properly, even across references.

The decimal conversion and deliberate rounding of values within entered formulae
where it is not absolutely necessary is highly unacceptable for a product which
will be used in a productive office environment (and sometimes even
inappropriately in mission critical environments. But blame human behavior for
that, not OOo.)

This is an opportunity for OOo to excel (pun intended) in reliability and
accuracy where others badly fail.

At least introduce a filter or additional abstract layer which preserves the
originally entered formula for reference and editing, like the user provided and
intended it, perhaps in a separate and prioritized data structure and only
evaluate it to decimal value upon display or user evaluation request or export
of the sheet into non-OOo formats.
This would allow for innovation while not breaking the apparently so highly
important adherence to bad habits of (still) inferior applications.

In the case at hand that lead to the filing of this problem (it is not an issue,
but that is a discussion to be held somewhere else), my original intention was
to evaluate the time spent for one action item within a larger assignment. There
is one column reserved for time in a matrix of action items. The result of the
addition of separate amounts of time within this cell need to be human
reviewable, so I (and my contractors) can see and edit how much time I spent in
how many distinct sessions working on a specific action item. The conversion to
decimals makes a quick, yet exact review impossible for the human eye. Where the
fundamental purpose of the application is to serve the user, it here fails.

And we are all for usability, are we not?

The ability to freely and intutively work with an application is a great
incentive in using it. This is an excellent and easy opportunity to make it happen.

[highly necessary rant and LAR]

There is a reason why I use and not Excel.

The attitude involved in the premature closing of this "issue" is not going to
make OOo better than Excel and is thus unacceptable. The consequence of
following a bad role model is that of ending up in the same dead end.

There will be no reason to switch over to OOo if the products become
indistinguishable. People need reasons to bother, not reasons for feeling
pampered. By pampering to bad habits and simply wrong and inappropriate
application behavior , you would attract the wrong people who would only pull
OOo down.

You do not want those. You should not bow and bend to them.
Not for what OOo stands for.
Comment 4 frank 2005-02-14 13:36:18 UTC
Hi Niklas,

please give us a short comment on this one and reflag and assign if necessary.

Comment 5 flibby05 2005-04-03 18:59:14 UTC
set to NEW,
oooqa can do nothing about this
Comment 6 niklas.nebel 2005-05-03 14:29:45 UTC
So we'll keep it as an enhancement issue.
Reassigning to owner "requirements".
Comment 7 ace_dent 2008-05-16 00:39:45 UTC Issue Tracker - Feedback Request.

The Issue you raised is currently assigned to 'Requirements' pending review, but
has not been updated within the last 3 years. Please consider re-testing with
one of the latest versions of OOo, as the problem(s) may have already been
addressed. Either use the recent stable version:
or consider trying the new OOo 3 BETA (still in testing):
Please report back the outcome so this Issue may be Closed or Progressed as
necessary - otherwise it may be Resolved as Invalid in the future. You may also
wish to search for (and note) any duplicates of this Issue that may have
advanced further by checking the Issue Tracker:
Many thanks,
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~