Ask Your Question

Problem with time calculations in Calc - LibreOffice 4.0 [closed]

asked 2013-05-04 23:58:09 +0200

DescartesBsb gravatar image

updated 2015-08-28 09:57:14 +0200

Alex Kemp gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-31 21:52:41.819038

3 Answers

Sort by » oldest newest most voted

answered 2013-05-05 08:51:49 +0200

froz gravatar image

updated 2013-05-05 08:55:00 +0200

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.

edit flag offensive delete link more


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.

oweng gravatar imageoweng ( 2013-05-05 12:10:49 +0200 )edit

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.

DescartesBsb gravatar imageDescartesBsb ( 2013-05-15 19:38:57 +0200 )edit

answered 2013-05-16 03:49:31 +0200

ROSt52 gravatar image

Triggered by the discussion I made a test in Calc and (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

edit flag offensive delete link more

answered 2013-05-05 07:32:12 +0200

oweng gravatar image

updated 2013-05-05 23:44:13 +0200

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.

edit flag offensive delete link more


This can't be date specifics...

froz gravatar imagefroz ( 2013-05-05 08:47:19 +0200 )edit

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.

oweng gravatar imageoweng ( 2013-05-05 11:42:28 +0200 )edit

Thanks a lot for changing your post. You rock.

froz gravatar imagefroz ( 2013-05-05 18:16:52 +0200 )edit

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

oweng gravatar imageoweng ( 2013-05-05 23:53:41 +0200 )edit

Question Tools


Asked: 2013-05-04 23:58:09 +0200

Seen: 3,955 times

Last updated: May 16 '13