Comparing Ron's answer to mine raises another question, how do you want
Excel to deal with A1=B1?




"Steve Dunn" wrote in message
...
Hi Ian,

That's because Excel sees 00:20 (without a date) as being less than 19:50,
and Excel doesn't recognise the viability of time-travel. i.e. you can't
have a negative expression of time.

Actually, I'm a little confused by your question; you state that you are
subtracting B1 from A1, but show your formula as B1-A1, which is the other
way around. For this I will assume you have start time in A1 and end time
in B1, so your formula would have been correct rather than your
description.

To get around your problem you have to let Excel know that 00:20 is
actually the next day, which you can do by adding 1 to it when B1 is less
than A1. This will achieve that:

=B1+(B1<A1)-A1


HTH
Steve D.


"Ian R" wrote in message
m...
Hi

I'm using Excel 2003.

I'm trying to calculate the total hours (C1) by subtracting end time
(B1) from start time (A1)

I have formatted the cells for time.

I'm currently using B1-A1 which works OK until the end time goes past
midnight.

e.g start 19:50 end 00:20 should = 4:30 except i just get an endless
series of #######

Thanks for your time.

Ian