# I'm stumped! [ CHART, see upload]

C:\fakepath\eM _Libre.ods =(a1)+(a2)

I'm unsure of this upload ... it should display > TOT #, in last column.

First time here? Check out the FAQ!

I'm stumped! [ CHART, see upload]

C:\fakepath\eM _Libre.ods =(a1)+(a2)

I'm unsure of this upload ... it should display > TOT #, in last column.

0

It displays 4 for me. Were you expecting 5? The time in cell H2 is negative although it does not show as such. If you change the formula in N2 to be =COUNTIF(H2:L2,"<0") it displays 1, i.e. one cell, H2 is less than zero.

If you change the formula to =ABS(SUM(C2-B2)), ignoring the sign, N2 displays 5.

**EDITED 30/01/2018**

@abi @Jim K Carrying out mathematical operations on times can cause problems. The mathematics works and the result displayed as a number will be correct but the result displayed as a time may not be as expected. In the posters sheet the time difference between 00:20 and 19:00 is 18 hrs 50 min. As the later time is subtracted from the earlier time the result is negative. It displays as 05:10, which is wrong, but as a number it is -0.78 which is correct (0.78 of a day, 18 hrs 50 min). If fact the display of 05:10 if taken as minus is 18:50 (midnight going back 5:10.). The standard format for a cell as time is to represent a clock time and as there is no meaning of a negative clock the time is shown incorrectly.

There is however a solution to this. The result of adding or subtracting times is a time period not a clock time. If the custom time format of [H]:MM is applied to the result cell the display shows correctly -18:50. This format creates a time duration in hours and minutes and not a clock time.

If the poster is only interested in the time difference and not the sign of it then the formula =ABS(C2-B2) can be used. @Jim K said this gives the wrong time of 06:50. This is likely that the time format is set to 12 hr AM/PM and the time is 06:50 PW which is 18:50.

There is useful information on this and other matters at https://forum.openoffice.org/en/forum...

as there is no meaning of a negative clock

It's not true. The "negative time" is normal date+time, i.e., midnight of LibreOffice's base date minus specified part of a day. It has a well-defined value, specifically as you described: 0.78 of a day previous to the LibreOffice's base date.

@Mike Kaganski You can have a negative time period but not a negative time. If it is a time period the cell should be formatted as [H]:MM - time period - not as a time to display correctly.

There is no "negative time". There is a negative floating point value that is the internal date+time representation in the program, that is bound to date zero. It is so-called serial time, and it may well be negative.

0

Your spreadsheet has several problems. First of all, 1 equals one day when formatted as a time, and a decimal is part of a day.

- B2 = 07:00:00 PM = 0.791666666666667
- C2 = 12:10:00 AM = 0.00694444444444444

So, **C - B** = -0.784722222222222, which is negative. As a result, `=COUNTIF(H2:L2;">=0")`

produces 4, because the other four values are positive or zero.

The solution is to set H2 to `=IF(C2>=B2;C2-B2;1+C2-B2)`

. Drag this formula to fill to L2.

Then to get the average times that are not zero, the formula in M2 should be `=SUM(H2:L2)/COUNTIF(H2:L2;">0")`

. AVERAGEIF includes durations that equal zero, which does not seem to be what you intended.

Finally, if `TOT #`

means the total number of non-zero breaks, then set N2 to `=COUNTIF(H2:L2;">0")`

.

Asked: ** 2018-01-27 14:20:39 +0200 **

Seen: **61 times**

Last updated: **Jan 30 '18**

Function that returns an array values

How can I put two data series with different categories in one chart? [closed]

Floating column chart? [closed]

inserting fractions in the calc cell

Calc: Convert text to link [closed]

Button in Macro to Print a Sheet

How use LibreOffice with high contrast?

Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.

What are the desired results? What does "> TOT #" mean? See guidelines for asking.

That user always ciphers messages. And asking the user to write in normal language doesn't help.