Elapsed time in days

[Steve M via OfficeKB.com]

Cell A1 is Jan 1, 2005 0:00
Cell A2 is Feb 28, 2005 00:00

I want cell A3 to calculate the elapsed time in days shown as "58:00:00".
How do I format cell A3?

 

[Bernie Deitrick]

Steve,

There is no format that will do that. You could use a formula:

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

but Excel will interpret the result as hh:mm:ss if you try to do further math using it.

HTH,
Bernie
MS Excel MVP

 

[Steve M via OfficeKB.com]

Thanks Bernie that's what I wanted.

Steve,

There is no format that will do that. You could use a formula:

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

but Excel will interpret the result as hh:mm:ss if you try to do further math using it.

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

Cell A1 is Jan 1, 2005 0:00
Cell A2 is Feb 28, 2005 00:00

I want cell A3 to calculate the elapsed time in days shown as "58:00:00".
How do I format cell A3?

0":00:00"


--ron

 

[Bernie Deitrick]

Ron,

Actually, no. That will round the days up incorrectly when the hours are more than 12.

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

Ron,

Actually, no. That will round the days up incorrectly when the hours are more than 12.

HTH,
Bernie
MS Excel MVP

I probably misinterpreted the OP's question, thinking that the times would
always be 00:00 as he posted; hence there would never be fractional days.


--ron