[Issue 127850] New: Time Calculation using formulas

[bu...@apache.org]

https://bz.apache.org/ooo/show_bug.cgi?id=127850

          Issue ID: 127850
        Issue Type: DEFECT
           Summary: Time Calculation using formulas
           Product: Calc
           Version: 4.1.5
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: Normal
          Priority: P5 (lowest)
         Component: ui
          Assignee: issues@openoffice.apache.org
          Reporter: r.ch@live.com
  Target Milestone: ---

Created attachment 86486
  --> https://bz.apache.org/ooo/attachment.cgi?id=86486&action=edit
OpenOffice Calc file showing bug in result of series of formulas.

Example: Time from 14:00 to 22:00 total 8 hours - formula works fine, no
issues.
 From 19:00 to 07:00 total 12 hours, shows correct number, but following
formulas will bug. For example if I want it to multiply total time (12 hours)
by $8 it should show $96, but it will show wrong number. But it shows correct
number if it's a day shift.

Also it doesn't like to use commas in formulas. So have to round up mileage...

 I attach a part of my spreadsheet which shows a bug if you create a formula to
calculate total time and the result is used in next formula of a following
cells. As soon you use different date times, it bugs.

 Also if you look at RATE it is in General Numbers instead of Currency. If I
use Currency in formula, it will show #VALUE in TOTAL INC.

-- 
You are receiving this mail because:
You are the assignee for the issue.

[Issue 127850] Time Calculation using formulas

[bu...@apache.org]

https://bz.apache.org/ooo/show_bug.cgi?id=127850

oooforum (fr) <oo...@free.fr> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |CLOSED
                 CC|                            |oooforum@free.fr

-- 
You are receiving this mail because:
You are the assignee for the issue.

[Issue 127850] Time Calculation using formulas

[bu...@apache.org]

https://bz.apache.org/ooo/show_bug.cgi?id=127850

Peter <le...@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |leginee@gmail.com
         Resolution|---                         |NOT_AN_OOO_ISSUE
             Status|UNCONFIRMED                 |RESOLVED

--- Comment #1 from Peter <le...@gmail.com> ---
Aß dritten in Mail it is all a missunderstanding.if it helps Herr is the
explanation again. If Three See questions Please send a Mail to Users Le dev
Mailing List.

This accounts for cases where the end time (H4) has a smaller clock value (e.g.
07:00) than the start time (e.g. 19:00) but is in the next day. Times are
stored in units of days. The time 07:00 has the value 7/24 and the time 19:00
has the value 19/24. You were subtracting
7/24 - 19/24 = -12/24 = -0.5
If a cell is formatted as time, -0.5 is displayed as12:00, but the value is
still -0.5. 
I appended
+ (G4 > H4) 
which returns FALSE (equal to zero) when H4 is larger than or equal to G4. When
G4 is larger than H4, it returns TRUE (equal to one) that gives
H4-G4 + (G4 >H4) = 7/24 - 19/24 + (19/24 > 7/24) = -0.5 + (TRUE) = -0.5 + 1 =
0.5
The 0.5, when formatted as time, is 12:00 and it is the desired answer. This is
not a bug. All spreadsheets treat times as fractions of a day.

Closing AS mit an issue.

-- 
You are receiving this mail because:
You are the assignee for the issue.