Let's say A1 contains a value which is the number of days in duration. Example:
That number represents the number of days.
I would like to use an excel format which ultimately makes it look like this:
i.e. 57 days, 2 hours, 55 minutes and 30 seconds.
Now, if I were to simply interpret number as a DateTime value and use a format text such as: m/d/yy hh:mm:ss
Then Excel will display it as:
Since internally Excel DateTime values are numerically the number of days since the start of the century (Date zero), this makes sense.
So to get the number of days since that date, and the start of the century, I can use the formula:
=DATEDIF(0, A1, "D")
which would give me 57
And then I can further construct what I ultimately want using a formula:
=TEXT(DATEDIF(0, A1, "D"),"0")&"."&TEXT(A1,"hh:mm:ss")
And I will get what I want, namely:
The the problem is that that's a formula, it's not a format text. That formula actually constructs the value and produces text.
I would like to know if there's a way to do this as a number format only.
The reason is because I would like to use it as a calculated column in Excel and then in pivot tables, and I don't want to lose the numeric'ness of it so that it can be further operated on. And I want to do it without an additional field.