Hi All,
Does anyone here know of a clever way (or dumb way) to force a cell to display a numeric time value as a separated out "Days + “hh:mm:ss.000”.
In other words, I would like to be able to take this numeric time (needed as a number for calculating averages etc):
63:09:30.000
and without changing the underlying number, “display” it as something like:
2 days 15:09:30.000
Any help is much appreciated.
Regards.
CT
=INT(A1) & " Days " & TEXT(A1; "HH:MM:SS.000" )
Impossible. Use an auxiliary column with a formula provided by others.
Hi @PKG ,
That looks like a brilliant solution. I’ve not come across that function before (but then I’m a newb, so I haven’t come across most functions yet 
).
Does this allow cells formularised with this to still be averaged, summed etc? Or will I need to use a helper column instead?
I assume from what @mikekaganski has written below, entering this as a custom fomat code won’t work??
Thanks again.
Regards,
CT
Hey @fpy ,
Thanks for the link.
So, it’s an age old problem. I’m not sure I fully followed what was being arrived at over on the thread you linked to.
Thanks again.
CT
in short :
→ no format code for a number of days ([DDD] not supported in ODF)
→ can’t use function / formula as custom format
more details from the links provided then.
Thanks again @fpy,
I re-read it again, and I get it more, now that I’ve had to spend time trying to engage with/work around this ridiculous situation.
Ta for the info and link. Much appreciated.
Regards.
CT
PS,
I’m losing count of how many flippin’ “helper” columns I’ve had to press into play now!!?! 
Spreadsheets are designed to work with columns. It is a good practice to not squeeze complex calculations into a single cell - use of multiple columns is methodologically correct. The wish to minimize number of columns is working against the system. Here you have just one more.
Thanks again @mikekaganski for your always awesome help.
I’m certainly coming to understand that what you say is true. Every day’s a learning adventure in “Spreadsheet Wrangling World®”!!?! 
Yes, so far.
And columns are cheap,
So are new sheets,
But also cheats.
In fact correct handling of data containing numeric values requires, imo, that the representation also contains sufficient and truthful information about the semantics. 1 = 1 V = 1 USD isn’t even fun, but a collection of lies. This is extremely important concerning DateTime values where not only the format is problematic, but also an arbitrary NullDate is applied, and makes the conventional format absolutely inapt for the communication of durations. Moreover ToD formats suppress an integer part. And so on …
Years ago I came to the conclusion that so-called numeric data must in most cases be exchanged as standardised texts. Pure numbers are a rare thing in the universe.
Modern processors, on the other hand, are fast enough for spreadsheet if textually represented data must be converted on the fly if calculations are to be performed. In addition an automatic check for the compatibility of operands would be needed.
I made a little example using some Basic code to get a kind of “proof of concept”. Now I derived an .ods from it containing a sheet specialised to durations and their relation to DateTime values.
Now you will definitely want to see my examples. The magic lies in the strings I use as xFormat - and in the related analysis, and …
xFormat_requiresREGEXfunction.ods (33.7 KB)
Don’t hesitate to ask if I failed to make the meanings/usage of xFormat strings clear enough.