Villeroy wrote:"DD HH:MM:SS" does not work because the day number "DD" is not the same as the number of days.
Yes, it's a calculated date element of the result.
Villeroy wrote:I believed "# HH:MM:SS" could do that, since "#" shows the integer part of your calculation result and "HH:MM:SS" is a way to show the part behind the dot (time as fraction of a day).
Unfortunately, Calc does not accept "# HH:MM:SS" as valid umber format string.
Yes, I'd tried that.
Villeroy wrote:I would use the calculation result in whatever number format for further calculation and if I desparately need to display "# HH:MM:SS" I would use a conversion to text: =TEXT(A1;"#")&" "&TEXT(A1;"HH:MM:SS").
brain kicks into gear! Doesn't work exactly like that though, as the days part is rounded up, e.g. 2.9 days (2 days, so many hours) shows as: 3 HH:MM:SS.
So, how about =TEXT(ROUNDDOWN(B2-A2;0);"#")&" days, "&TEXT(B2-A2;"HH:MM:SS")
or even =TEXT(ROUNDDOWN(B2-A2;0);"#")&" days, "&TEXT(B2-A2;"H")&" hours, "&MINUTE(B2-A2)&" mins, "&TEXT(B2-A2;"S")&" secs"
- couldn't get TEXT(B2-A2;"mm") to work, refers to month.