7

I need to calculate the difference between two Date Time fields in LibreOffice Calc, with the answer in hours, positive or negative. When there is a positive result =J2-I2 works, and returns answer in hours. When there should be a negative result it does not, and instead of returning 23:xx it returns 17:xx, and not even the correct integer to suggest any kind of logic. How would I craft a simple formula so that answers are returned in the form of -23:59 - 23:59 as the range of possible time differences? No roll-over, just positive/negative. The difference would never be more than +/- 23:59...

CedarSage
  • 73
  • 1
  • 1
  • 3
  • Could you please give a complete example with input values and expected / wrong result? – tohuwawohu Sep 01 '17 at 15:36
  • 2018-01-09 07:50:00 - 2018-01-09 00:52:00 = 17:02:00 with formula =J24-I24, I'm just shooting in the dark, but what I need is -06:58. Other rows in the column are positive, so subtracting 24 would modify them undesirably. – CedarSage Sep 01 '17 at 16:11

1 Answers1

6

The important part for working with time values as well as dates in LibreOffice (same is true for Excel) is to understand that these are just normal numbers with a special number format.

The value 1 represents 24 hours when formatted as time or one day past the zero date (can be changed in the options). Now if you add the default time format you'll get something like 12:00:00 AM (for an en-US locale, for other locates the representation looks different).

The second important thing to understand is that the number format code tells you how your value is interpreted. The number format code that corresponds to 12:00:00 AM is HH:MM::SS AM/PM which says that the time should be calculated modulo 24 hours and AM/PM applied automatically.

In your case you want to show negative time (or time differences) so you want to represent negative numbers like -0.1 as -02:24:00 which requires you to adapt the number format code. An easy example would be to use [HH]:MM:SS (am/pm makes no sense for time differences) which tells the number formatter that you don't want to calcuate times modulo 24 hours. With this number format code you can represent any number as a time difference.

moggi
  • 1,466
  • 4
  • 18
  • 29
  • You need a formula that returns a negative value. Taking your example with 2018-01-09 07:50:00 - 2018-01-09 00:52:00, just change the formula from =J24-I24 to I24-J24. Note that 2018-01-09 07:50:00 is a larger value than 2018-01-09 00:52:00. – moggi Sep 01 '17 at 18:05
  • Ok, so I get all the words you said in that .1 day is 2:24:00, and we are editing a duration. The first cell is a date time, not a duration, so is the second, only the third is a duration, which means that something happend xx:xx hours/minutes ago. So thank you very much for the above, but is there anything I can do to the formula =J24-I24 that will provide the result -06:58:00 ? I need to do this formula for about 1000 cells per sheet for 6-20 sheets, so I'd really like to click and drag on this one... – CedarSage Sep 01 '17 at 18:07
  • You need to think about how you want to calculate your time difference. Both + 06:58:00 and -06:58:00 are correct values depending on how you look at it. One corresponds to about 7 hours after the earlier point in time, and the other one 7 hours before the later point in time. Apply your formula accordingly and fill the whole column with it. After that mark the column and go to Format->Cell and select the time number format with [HH]:MM:SS. – moggi Sep 01 '17 at 18:20
  • That worked splendidly, thankyou. It was unclear to me earlier if [hh] was for the formula bar or what but for other newbies you enter this into the "format code" area of "format cell", and it works, even though duration isn't obvious as a category. – CedarSage Sep 01 '17 at 18:26
  • Could you add a link to LibraCalc documentation please? I have a case that spans days and this solution does not work satisfactorily. – gone Oct 29 '22 at 07:51