How do I format the number in a cell as a duration?

[EyalRozenberg]

I have a cell which is the difference between two time cells - a duration.

I want to format it in one of two ways:

1. Always show years, months, days, hours, minutes and seconds of difference.
2. Only show the maximum unit in which the difference is non-zero, and one extra unit. So, if the difference is 25 hours, I should see 1 day 1 hour and if it’s 23 hours, I should see 23 hours 0 minutes.

as for spacing, separators, field indicators - make it whatever is common and you believe will be easy to understand.

[mikekaganski]

Without formulas and macros, the only way to format durations is to use the square brackets around the largest used unit allowing unambiguous “duration” representation: hours, minutes, or seconds.
These are examples of valid duration formats:

[HH]:MM
[H]:M:S
[MM]:SS.000

Note: the dot in the last sample is actually a locale-specific decimal separator, so could be [MM]:SS,000 in some locales (as e.g. Russian).

Unfortunately, days are not among that list, even though days arguably allow unambiguous understanding of the time span they represent. OTOH, months and years don’t allow unambiguous understanding, given their varying lengths, so it wouldn’t make sense to implement duration formats with those.

Also, there’s no built-in way to only show highest (in fact, any) duration unit optionally.

Of course, with formulas you can format numbers to strings in whatever way you want (with your arbitrary “month/year duration” definition).

[mariosv]

Bug 163034 - Add Duration number format category

[EyalRozenberg]

Yes, I filed that bug after asking here…

[EyalRozenberg]

Thanks… but even the general number format beats this when it’s date differences - because I at least get a day resolution

But this is helpful for shorter durations or where hours-or-less are what we want.

[Villeroy]

qa111093.ods (21.7 KB)

[Lupp]

In science and engineering single-unit measures for time are prefered. The used unit is next to always the SI second then.
At least as soon as days come into the picture, the usual “DateTime” formats with seconds or even fractions thereof become meaningless. Since the introduction of UTC alone, there have been almost 40 days with 86401 seconds (with a leap second).
However, also ISO 8601 specifies a duration format (as a subcase of periods). It is P(n)Y(n)M(n)DT(n)H(n)M(n)S if I’m informed correctly.
Each (n) stands for a number there.
If a complete duration format for everyday use should be added to LibO NumberFormat collections, the ISO standard should be regarded as far as possible with respect to human reading. The mess with date-time we have already does not need additions.
(To ease reading by humans the "T" may be replaced with a space or an underscore.)
Just for fun I made an example for the coversion/reconveresion using a very similar format. (The ambiguous usage of the upper case “M” by ISO is something I can hardly adapt to. Human readability will require a few additional spaces. That shoul be within the small range of variability.)
disask111093_FancyDurationFormat.ods (17.6 KB)