Date/Time Question
 

I have four columns:

A B C D
[Date] [Time] [Date] [Time]
01/01/2005 01:00 01/01/2005 01:05
01/01/2005 01:00 01/02/2005 01:05
01/01/2005 01:00 01/03/2005 01:05
How can I get in column "E" a formula that produces the different in time?
1. (A1+B1) - (C1+D1) = 0:00:05
next one
2. (A2+B2) - (C2+D2) = 0:24:05
next one
3. (A3+B3) - (C3+D3) = 1:24:05
I used this formula but got "#value"
=TEXT((A1+B1)-(C1+D1),"d:h:mm")
Can someone help?

Date/Time Question
 

On Wed, 9 Nov 2005 16:32:20 -0800, bladelock
wrote:

I have four columns:

A B C D
[Date] [Time] [Date] [Time]
01/01/2005 01:00 01/01/2005 01:05
01/01/2005 01:00 01/02/2005 01:05
01/01/2005 01:00 01/03/2005 01:05
How can I get in column "E" a formula that produces the different in time?
1. (A1+B1) - (C1+D1) = 0:00:05
next one
2. (A2+B2) - (C2+D2) = 0:24:05
next one
3. (A3+B3) - (C3+D3) = 1:24:05
I used this formula but got "#value"
=TEXT((A1+B1)-(C1+D1),"d:h:mm")
Can someone help?

You must subtract the earlier time from the later time. You are doing the
reverse.

Also, be aware that the maximum "d" is 31. If your calculations result in a
higher "d", it will be erroneous. If that may be a problem, try:

=INT(C2+D2-(A2+B2)) &":" & TEXT(MOD(C2+D2-(A2+B2),1),"hh:mm")

If you MUST have negative times, there are some workarounds, so let us know.


--ron

Date/Time Question
 

Thank you

"Ron Rosenfeld" wrote:

On Wed, 9 Nov 2005 16:32:20 -0800, bladelock
wrote:

I have four columns:

A B C D
[Date] [Time] [Date] [Time]
01/01/2005 01:00 01/01/2005 01:05
01/01/2005 01:00 01/02/2005 01:05
01/01/2005 01:00 01/03/2005 01:05
How can I get in column "E" a formula that produces the different in time?
1. (A1+B1) - (C1+D1) = 0:00:05
next one
2. (A2+B2) - (C2+D2) = 0:24:05
next one
3. (A3+B3) - (C3+D3) = 1:24:05
I used this formula but got "#value"
=TEXT((A1+B1)-(C1+D1),"d:h:mm")
Can someone help?

You must subtract the earlier time from the later time. You are doing the
reverse.

Also, be aware that the maximum "d" is 31. If your calculations result in a
higher "d", it will be erroneous. If that may be a problem, try:

=INT(C2+D2-(A2+B2)) &":" & TEXT(MOD(C2+D2-(A2+B2),1),"hh:mm")

If you MUST have negative times, there are some workarounds, so let us know.


--ron