Share via

Converting dd:hh:mm:ss to days

Anonymous
2016-10-17T20:47:23+00:00

I have a cell that contains data in dd:hh:mm:ss and would like a quick formula to convert to days with a decimal point. 

Example:  10:16:45:51

Convert to:  10.7  days

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-10-17T23:37:59+00:00

Teylyn wrote:

B2 here does not contain text. It is a time value formatted with custom format "d:mm:hh:ss". That's how I read the question, but you're right, it's probably text. 

No, you're right.  Watts wrote "contains data in dd:hh:mm:ss", which is ambiguous.  I interpreted it to mean:  data entered in that form, which Excel would interpret as text.

@Watts:  If Teylyn is correct and the cell contains a numeric value formatted as dd:hh:mm:ss, bear in mind that "dd" might work for you only by coincidence.  Generally, it should not be used that way, IMHO.

"dd" is day of the month, not number of days.  It might seem to work when the numeric time is less than 32 days (after rounding any fractional second) only because the integer part represents a day in Jan 1900 (!).

If the numeric time were 32d 2h 3m 4s, for example, it would appear as 01:02:03:04, not 32:02:03:04 as you would intend, I presume.

Was this answer helpful?

2 people found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-10-17T22:59:47+00:00

    Hello,

    just format the cell as a number with 1 decimal.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2016-10-17T21:02:07+00:00

    If each part in A1 is always 2 digits (1d 2h 3m 4s is 01:02:03:04; [edit] and dd<=99), then:

    =LEFT(A1,2) + RIGHT(A1,8)

    More generally:

    =LEFT(A1, FIND(":",A1)-1) + MID(A1, FIND(":",A1)+1, 99)

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2016-10-17T23:25:38+00:00

    B2 here does not contain text. It is a time value formatted with custom format "d:mm:hh:ss". That's how I read the question, but you're right, it's probably text.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-10-17T23:16:15+00:00

    Teylyn wrote:

    just format the cell as a number with 1 decimal.

    "Just format"?!

    Your example should not work because (my) Excel does not recognize 10:16:45:51 as time, much less 10d 16h 45m 51s.  10:16:45:51 is text, not a numeric value.  So why does it seem to work in your Excel?

    Aha!  What is in B3? I'm guessing is not simply =B2. ;-)

    Was this answer helpful?

    0 comments