time subtraction problem

[Guest]

I have two times,
22:19 pm and 1:50 am
obviously 1:50 is after 22:19 but when I try to subtract it --> 22:19 - 1:50
excel gives me gibberish.

thnks.

 

[Bob Phillips]

=MOD(B1-A1,1)

 

[Govind]

Hi,

Lets say your start time is in cell A1 and end time is in B1, use the
formula

=IF(B1<A1,B1+1-A1,B1-A1)

and format the cell in the time format you wish.

Regards

Govind.

 

[Guest]

thanks.

both formulae worked,
but I would prefer the IF statement even though its abit longer.
since it applies to both cases of A1 > B1 or B1 > A1, i can drag the formula
down the whole column.

BTW, what does (time + 1) do to the smaller time?

thnks.

 

[Govind]

Hi,

Thanks for the feedback. The +1 adds one day to the smaller time.

Govind.

 

[Roger Govier]

You can also copy Bob's formula down the column. It will work with both
cases.

 

[Bob Phillips]

both formulae worked,
but I would prefer the IF statement even though its abit longer.
since it applies to both cases of A1 > B1 or B1 > A1, i can drag the formula
down the whole column.

and mine doesn't?