Excel custom format seconds as dd:hh:mm:ss but hide leading zero values

Question

Is there an Excel custom format string that will show seconds in a dd:hh:mm:ss format, but hiding any leading zero values?

E.g., it is straightforward to format 100,000 s in dd:hh:mm:ss as 01:03:46:40. However, for 50,000 seconds I would like to display as hh:mm:ss (i.e. 13:53:20) not as dd:hh:mm:ss (00:13:53:20), using a single formatting string for all values.

It can be achieved using TEXT formulas with something like:

IF(A1<60, 
TEXT(A1/86400, "ss"), 
IF(A1<3600, 
TEXT(A1/86400, "mm\:ss"), 
IF(A1<86400, 
TEXT(A1/86400, "hh\:mm\:ss"), 
TEXT(A1/86400, "d\:hh\:mm\:ss"))))

However this returns all values as text which doesn't allow numerical sorting of the values.

It can also be achieved with conditional formatting, but I'd prefer to use that only if I have to.

I'm hoping someone is going to tell me about the magical custom format string that will do the job!

Answer 1

Dates are stored as days and fractions of a day.

So, no matter what, 50,000 in Excel means 50,000 days. Before you can format it to show the equivalent hrs/mins/sec you must divide it by 86,400 to transform the value to 50,000 seconds

Once you have done that, you can use a custom number format: [<1]hh:mm:ss;dd:hh:mm:ss

However, you should also be aware the dd will only be in the range of 1-31. It will roll over back to 1 at that point. So if your time will encompass more than 31 days, you will not be able to use a custom number format to accomplish what you want.