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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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+(A1>B1)

or

=MOD(B1-A1,1)

Time in XL is fraction of a day so a whole day id equal to 1.
If the +(A1>B1) 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 +(A1>B1) 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
[email protected]
Replace@mailinator with @tiscali.co.uk


"kvani" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>