Ask Your Question

I'm stumped! [ CHART, see upload]

asked 2018-01-27 14:20:39 +0200

abi gravatar image

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

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

edit retag flag offensive close merge delete


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

Jim K gravatar imageJim K ( 2018-01-27 18:29:45 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-27 19:03:55 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-01-27 19:00:33 +0200

peterwt gravatar image

updated 2018-01-30 16:59:36 +0200

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

edit flag offensive delete link more


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.

Jim K gravatar imageJim K ( 2018-01-27 19:25:18 +0200 )edit

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 gravatar imageMike Kaganski ( 2018-01-30 17:14:23 +0200 )edit

@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.

peterwt gravatar imagepeterwt ( 2018-01-30 19:20:58 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-30 19:56:48 +0200 )edit

answered 2018-01-27 19:20:27 +0200

Jim K gravatar image

updated 2018-01-27 19:23:04 +0200

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").

edit flag offensive delete link more


See my edited answer.

peterwt gravatar imagepeterwt ( 2018-01-30 17:00:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 82 times

Last updated: Jan 30 '18