Calc fails to correctly format a date?

[jyoung]

Hi everyone,
I use Libre Calc 6.0.1.1 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

[mikekaganski]

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)

[Lupp]

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 ISO 31-1 - Wikipedia.
If you are fond of international standards, also see ISO 8601 - Wikipedia.
Personally I would be a bit less strict and more friendly to the human eye in this case.

[mikekaganski]

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

[jyoung]

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!

[erAck]

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.