Calculate time difference in excel

问题:

I have a two dates in excel "1/2/2016 01:56:05" and "8/3/2016 06:21:46". How do I calculate the time difference between them in a format of days and hours?

Thanks!

回答1:

Try this to include the spelled out hours and minutes:

=INT(A2-A1) & " days, " & TEXT(A2-A1,"h "" hours, ""m "" minutes""")

Note that since the m comes immediately (ignoring the separator text in quotes) after the h it will be interpreted as minutes and not months

回答2:

I prefer to work with dates as decimals. It looks cumbersome, but I'm much happier knowing what it's doing.

What I would usually do is to have A1-A2 in cell A3, then work out the component parts separately:

Days: =INT(A3)
Hours: =INT((A3-INT(A3))*24)
Minutes: =INT(((A3*24)-INT(A3*24))*60)

Or if you wanted to do it all in one formula:

=INT(A3)&" days, "&INT((A3-INT(A3))*24)&" hours, "&INT(((A3*24)-INT(A3*24))*60)&" min"

or without using A3

=INT(A1-A2)&" days, "&INT(((A1-A2)-INT(A1-A2))*24)&" hours, "&INT((((A1-A2)*24)-INT((A1-A2)*24))*60)&" min"

It's not the prettiest or most efficient method but you can see how the times are calculated this way.

回答3:

Assuming the dates are in cells A1 and A2, this will produce an answer with minutes as well in d:hh:mm format.

=INT(A2-A1) & ":" & TEXT(A2-A1,"hh:mm")

Drop the :mm if you don't need minutes.

If you want text:

=INT(A2-A1) & " days, " & TEXT(A2-A1,"h") & " hours"

If you want text with minutes:

=INT(A2-A1) & " days, " & TEXT(A2-A1,"h"" hours, ""m"" minutes""")

Using double quotes alongside each other "escapes" the quote itself and allows the extra text to appear in the string. As Ron stated in his answer, the m following an h in the same format string indicates minutes, so you can save an extra A2-A1 calculation by putting both in a single format.