Format a time span as a number of days, hours and minutes

Question

This is purely an Excel sheet question.

I have a time span that is basically a difference between two cells, each containing a date:

I have managed to get the time difference in number of hours and minutes by changing the number format to [h]:mm. Now I would like to get the difference in number of days, hours and minutes. I tried to set the number format to [d]:[h]:[mm] but it is not accepted by Excel.

Here's what I have now:

    A                   B                   C (=A2-A1)
    ----------------    ----------------    ----------
1|  14/10/2011 00:00    17/10/2011 07:50    79:50
2|  14/10/2011 00:00    17/10/2011 11:00    83:00

Here's what I would like to get:

    A                   B                   C (=A2-A1)
    ----------------    ----------------    ----------
1|  14/10/2011 00:00    17/10/2011 07:50    3:7:50
2|  14/10/2011 00:00    17/10/2011 11:00    3:11:00

How can I do that? Is there a number format available for that?

Answer 1

You can use TEXT
=TEXT(B1-A1,"d:h:mm")

Note the same effect can be achieved using a simple number format on the cells directly

• select your range (cells C1, C2 etc)
• right click and Format Cells
• Custom
• Type d:hh:mm

If unlike your example data, your date differences exceed 31 days, then an approach such as =INT(B1-A1)&":"&TEXT(B1-A1,"h:mm")
will work

Answer 2

Warning: the above only works for ranges less than 31 days. use

=CONCATENATE(TEXT(FLOOR(B1-A1,1),"@")," Days",TEXT(B1-A1," h:mm:ss"))

instead for ranges above 31 days. This will not sort well, so it would be better to do the calculation in one column and then prettify that column for the end result.

Answer 3

Unfortunately it appears number and datetime formats cannot be combined, otherwise a custom format of:

0:h:m

would be the ticket. However, if for spans of 32 days or more, you are satisfied with just displaying the number of (fractional) days you can use a custom format like:

[>=32] 0.00 "days"; d:h:m

The cell value remains numeric.