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