Problem with time calculations in Calc - LibreOffice 4.0

In previous versions of LibreOffice, for instance:
03:00:00 (3 hours, zero minutes, zero seconds) minus 04:00:00 would give -01:00:00.
Now in LibreOffice 4.0, the calculation is done “modulo 24 hours”, and the result is 23:00:00.
Is it something dependent on a particular setting which changed its default, or is it a bug?

You most probably have a format cell problem. Right click on cell with formula and select Clear Direct Formatting.

You can check formatting with right click on cell and Format Cells. In default Number tab for formula cell there should be Category=Number and Format=Standard. You most probably have Category=Time and Format code=HH:MM:SS.

If formula cell has a format of Number then calculates 3 - 4 = -1, but if format is Time then 03:00:00 - 04:00:00 returns 23:00:00.

I now think the issue might be related to using the HH:MM:SS format vs the [HH]:MM:SS format. The former shows 23:00:00 here, while the latter shows -01:00:00. Default format also shows -01:00:00. Thanks for bringing this to my attention.

froz, you correctly diagnosed the cause of the problem.
Nevertheless, that means something really changed from LibreOffice3 to LibreOffice4.
For the same spreadsheet, in LibreOffice 3.6.5 the “direct formatting” formats the cell with the formula as “number/standard”, while for LibreOffice 4.0.2 it is “time/HH:MM:SS”.
Anyway, with your help I was able to adapt the spreadsheet to work with LibreOffice4 the same way it did with LibreOffice3. Thank you very much.

You don’t mention your platform, LO version, or file format, however this is possibly caused by your settings under Tools > Options… > LifeOffice Calc > Calculate > Date being set to the 01/01/1904 value rather than the default 12/30/1899 value. I can exchange ODS documents here between v3.5.7.2 and v4.0.2.2 without issue using the basic arithmetic you provide. Both computers however are set to the 12/30/1899 value.

EDIT: As @froz pointed out this is not a calculation setting. I was playing around with passing the ODS back and forth between v3.5.7.2 and v4.0.2.2 and somewhere must have slipped up. Sorry.

This can’t be date specifics…

I think you are right. I don’t know what happened in my original test. I managed to consistently obtain a value of “23:00:00” and now I cannot repeat it. All my tests come back with “-01:00:00”. I will amend my answer accordingly.

Thanks a lot for changing your post. You rock.

Well, if I’m wrong, I’m in need of correction. Can’t be right all the time :slight_smile: I have up-voted your answer as it appears this is indeed the issue.

Triggered by the discussion I made a test in Calc 3.6.6.2 and 4.0.1.2 (portable)

As pointed out by @froz and @oweng the format has an influene to the result. As @DescartesBsb pointed to different versions of LibO I made above mentioned test.

Here are is the result:130516_TimeFormatComparision401&366.odg