How to calculate Date Time Difference in LibreOffice Calc

Question

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

Solution

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.