eM _Libre.ods =(a1)+(a2)
I’m unsure of this upload … it should display > TOT #, in last column.
That user always ciphers messages. And asking the user to write in normal language doesn’t help.
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 @jimk 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. @jimk 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 [Tutorial] Ten concepts that every Calc user should know (View topic) • Apache OpenOffice Community Forum
Yes, I tried =ABS(SUM(C2-B2)) at first also, but this shows 06:50 as a time, which does not make sense when formatted this way. Also SUM is unnecessary. So I believe the intention is rather 1+C2-B2 instead (or 1-(B2-C2)), as shown in my answer.
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.
@mikekaganski 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.
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").
See my edited answer.