Time Difference
 

Hi,
I want to find the time difference between column1 & 2
I have indicated in the last column what I should be getting. Pls
assist.
Thank you and Rgds. :)

column1 column2 column1-column2 should be
23:45 00:15 23:30 minus 30mins
09:15 09:30 -00:15 correct
07:30 07:15 00:15 correct
00:15 23:45 -23:30 30mins


--
kvani
------------------------------------------------------------------------
kvani's Profile: http://www.excelforum.com/member.php...o&userid=27725
View this thread: http://www.excelforum.com/showthread...hreadid=472379

Your examples show the correct results. The difference between 00:15 and
23:45 on the same day is 23:30. If you want to cross date boundaries, you
can't expect Excel to assume you mean the following day. As to the solution,
I don't know myself but I have seen something about this elsewhere. If I can
find it I will post it.

Peter


"kvani" wrote in
message ...

Hi,
I want to find the time difference between column1 & 2
I have indicated in the last column what I should be getting. Pls
assist.
Thank you and Rgds. :)

column1 column2 column1-column2 should be
23:45 00:15 23:30 minus 30mins
09:15 09:30 -00:15 correct
07:30 07:15 00:15 correct
00:15 23:45 -23:30 30mins


--
kvani
------------------------------------------------------------------------
kvani's Profile:
http://www.excelforum.com/member.php...o&userid=27725
View this thread: http://www.excelforum.com/showthread...hreadid=472379

Try:

=B1-A1+(A1B1)

or

=MOD(B1-A1,1)

Time in XL is fraction of a day so a whole day id equal to 1.
If the +(A1B1) in the first formula is TRUE then XL converts it to 1 in the
addition so the formula is =B1-A1+1 which takes care of the fact that B1 is
smaller. If the time does not cross midnight then +(A1B1) will evaluate to
False which XL will change to 0 so it will not alter anything.

The second formula is not so eay to see and works because the MOD function
always retuns a positive even if the argument is negative.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"kvani" wrote in
message ...

Hi,
I want to find the time difference between column1 & 2
I have indicated in the last column what I should be getting. Pls
assist.
Thank you and Rgds. :)

column1 column2 column1-column2 should be
23:45 00:15 23:30 minus 30mins
09:15 09:30 -00:15 correct
07:30 07:15 00:15 correct
00:15 23:45 -23:30 30mins


--
kvani
------------------------------------------------------------------------
kvani's Profile:
http://www.excelforum.com/member.php...o&userid=27725
View this thread: http://www.excelforum.com/showthread...hreadid=472379