Time Difference

  • Thread starter Thread starter kvani
  • Start date Start date
K

kvani

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
 
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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
 
Back
Top