4

Let's say A1 contains a value which is the number of days in duration. Example:

57.121875

That number represents the number of days.

I would like to use an excel format which ultimately makes it look like this:

57.02:55:30

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:

2/26/00 02:55:30

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:

57.02:55:30

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.

2
  • Did you find an answer to that? I am in the same situation. I need to format it showing total days but it wraps at 31 – Thanasis Ioannidis Jun 14 '18 at 12:02
  • Thanasis, I don't believe there's a way to do it. – zumalifeguard Jun 14 '18 at 19:58
1

Just use custom format 'dd.HH:MM:SS'

5
  • Actually, "dd.HH:MM:SS"; the OP wants a period after the number of days. – G-Man Says 'Reinstate Monica' Mar 25 '15 at 21:53
  • +1 because I think this is the closest solution but it will only work correctly for durations less than one month. – Mike Fitzpatrick Mar 25 '15 at 22:06
  • less than 32 to be exact, it's still 17th of January – tbc Mar 25 '15 at 22:45
  • I can't accept the answer. The part before the period can be large numbers. I don't see how I can introduce wrong numbers in the report by doing this. I'm very sorry. – zumalifeguard Mar 26 '15 at 2:39
  • 1
    OK, you don't have to accept the answer if it does't fix your problem. But you should Edit your answer to describe your other problem with an example, your 17.121875 works fine with my answer, while 57.121875 doesn't – tbc Mar 26 '15 at 21:14
-1

Kind of like the first answer, but I compute the days (int formula), use concatenate (&) use your period separator, then convert the decimal portion to a time.

=INT(A1)&"."&TEXT(A1,"hh:mm:ss")
1
  • 1
    Excerpt from question: "The problem is that that's a formula, it's not a format. 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." – fixer1234 Aug 3 '19 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.