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

问题:

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?

回答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

回答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.

回答3:

The following is certainly not the shortest formula and not a direct answer to the question but works to show the correct result (also on LibreOffice) in the following format:

# days ## hours ## minutes ## seconds

=CONCATENATE(TEXT(FLOOR(B2-A2,1),"@")," days ", IF(HOUR(A12)-HOUR(A11)<0,HOUR(A12)-HOUR(A11)+24,HOUR(A12)-HOUR(A11))," hours ",IF(MINUTE(A12)-MINUTE(A11)<0,MINUTE(A12)-MINUTE(A11)+60,MINUTE(A12)-MINUTE(A11)), " minutes ", TEXT(B2-A2,"s"), " seconds")

回答4:

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.

回答5:

The following approach works as well, assuming that your start date is in cell C2 and your end date is in cell D2:

 =TEXT((D2-C2)-MOD(D2-C2,1),"0") & " days " & TEXT(MOD(D2-C2,1),"hh:mm")

回答6:

=IFERROR(INT(Date1-Date2)&" Days "&INT(MOD((Date1-Date2);INT(Datw1-Date2))*24)&" Hours "&MINUTE(Fate1-Date)&" Min";IFERROR((Date1-Date2);"NA"))enter code here

I think this should do the trick, whatever date you have.