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?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

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" wrote in message ...
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?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Thanks Bernie that's what I wanted.

Bernie Deitrick wrote:
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

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?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

On Mon, 08 Aug 2005 14:58:58 GMT, "Steve M via OfficeKB.com"
wrote:

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

Ron,

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

HTH,
Bernie
MS Excel MVP


"Ron Rosenfeld" wrote in message
...
On Mon, 08 Aug 2005 14:58:58 GMT, "Steve M via OfficeKB.com"
wrote:

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

On Mon, 8 Aug 2005 12:48:39 -0400, "Bernie Deitrick" <deitbe @ consumer dot
org wrote:

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