Date/Time Question

[Guest]

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?

 

[Ron Rosenfeld]

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

 

[Guest]

Thank you

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