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.

Improve this question
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, 2018 at 12:02
  • Thanasis, I don't believe there's a way to do it.
    – zumalifeguard
    Jun 14, 2018 at 19:58

2 Answers 2

Reset to default
1

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

Improve this answer
5
  • Actually, "dd.HH:MM:SS"; the OP wants a period after the number of days.
    – G-Man Says 'Reinstate Monica'
    Mar 25, 2015 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, 2015 at 22:06
  • less than 32 to be exact, it's still 17th of January
    – tbc
    Mar 25, 2015 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, 2015 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, 2015 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")
Improve this answer
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, 2019 at 7:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .