We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calc fails to correctly format a date?

asked 2019-12-31 14:46:39 +0200

jyoung gravatar image

Hi everyone, I use Libre Calc with Linux.

I'm trying to get a formatted delta time between two dates but I get result wrong. In cell A1 I put 31/12/19 13:20 (unformatted is 43830.5556815843) In cell A2 I put 02/01/20 14:00 (unformatted is 43832.5833333333)

the unformatted result is correct; cell A3 returns A2-A1 = 2.02765174899832 if I apply format DD HH:MM:SS result is 1 00:39:49 where obviously the time part is correct but the day part is wrong

Worse scenario is when I try to subtract 31/12/19 13:20 from 01/01/20 10:20. In this case the formatted result is 30 21:00:00 and not 0 21:00:00 as expected

There is a workaround by splitting the result into integer and fraction, keep integer as day and convert fraction to time and finally concatenate them, but is way so unproductive

Any guidelines what am I doing wrong or if this is a known bug?

Happy New Year to All Joe

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-12-31 15:27:31 +0200

updated 2019-12-31 19:08:09 +0200

Calc does everything correctly.

Format DD HH:MM:SS is a format for dates, i.e. for points in time represented in some calendar. This format tells "get a date and show its calendar day number, along with time; but skip its calendar month and year". For a date 2.02765174899832, it's a point in day two since epoch (day zero), which is by default 1899-12-30. So, day two is 1900-01-01, and you are shown that "-01" correctly when you use that format.

For time spans (as opposed to calendar dates and wall clock times), there are formats like [HH]:MM:SS or [MM]:SS or [SS] - but they are only applicable to time part, and you cannot apply that to days.

If you need to display the number specifically in days and time, you would need to use a formula like

=TRUNC(A2-A1;0) & " d " & TEXT(MOD(A2-A1;1);"HH"" h ""MM"" min ""SS"" s""")

(edited to conform to proper representation of durations, as noted by @Lupp)

edit flag offensive delete link more


I am stubborn insofar: HH:MM:SS or any formats containing a colon should not be used as duration fomats. They should be used for TimeOfDay and nothing else.
[HH]" h "MM" min "SS" s"may be acceptable (kind of). Better do it one of the ways shown in https://en.wikipedia.org/wiki/ISO_31-1.
If you are fond of international standards, also see https://en.wikipedia.org/wiki/ISO_860....
Personally I would be a bit less strict and more friendly to the human eye in this case.

Lupp gravatar imageLupp ( 2019-12-31 18:53:46 +0200 )edit

@Lupp: if you refer to the formula I wrote above:

=TRUNC(A2-A1;0) & " days " & TEXT(MOD(A2-A1;1);"HH:MM:SS")

then note that HH:MM:SS was not a duration (in a sense that it was just a part of the duration); in any duration format, the most significant part is declared "unbounded" (which [] denotes). In the formula, I faked a duration format of the kind [D]" days "HH:MM:SS, where the unbounded part is [D], not [HH]. So putting HH into square brackets here is wrong conceptually IMO.

EDIT: ah I see, the point is not [], but (absent) units. I agree now.

Mike Kaganski gravatar imageMike Kaganski ( 2019-12-31 19:01:43 +0200 )edit

Thank you very much Mike. I bet there is a good reason why day zero is NOT 1900-01-01 Your formula works excellent too!

jyoung gravatar imagejyoung ( 2020-01-01 03:57:21 +0200 )edit

I bet there is a good reason why day zero is NOT 1900-01-01

Null date is 1899-12-30 because MS-Excel uses 1899-12-31 as null date but wrongly includes 1900-02-29 as valid date although 1900 was not a leap year.

erAck gravatar imageerAck ( 2020-01-03 17:44:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-12-31 14:46:39 +0200

Seen: 148 times

Last updated: Dec 31 '19