Elapsed time in days

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" <[email protected]> wrote in message news:[email protected]...
> 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"
<[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 08 Aug 2005 14:58:58 GMT, "Steve M via OfficeKB.com"
> <[email protected]> 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

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" <[email protected]> wrote in message news:[email protected]...
> 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"
<[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> On Mon, 08 Aug 2005 14:58:58 GMT, "Steve M via OfficeKB.com"
> <[email protected]> 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