formula help please

A

Andrew

How would I write the formula to achieve this


These are times

a b c
6:32 7:30 -0:58

If I just enter the formula a1 - b1 all I get is ######## and not the
answer c

is there a certain way you have to enter a negative number

Thanks

Andrew
 
J

Jonathan Rynd

a b c
6:32 7:30 -0:58

If I just enter the formula a1 - b1 all I get is ######## and not the
answer c

is there a certain way you have to enter a negative number
Click to expand...

In Excel's mind, times can't be negative. So if you want that, you have to
convert the numbers to text. Try this for your formula. See if you can
figure out how it works.

=IF(A1-B1<0,"-","")&ABS( ROUNDDOWN(24*(A1-B1),0) )&":"&TEXT( 60*ABS(24*(A1-
B1)-ROUNDDOWN(24*(A1-B1),0)), "00" )
 
A

Andrew

Jonathan Rynd said:
In Excel's mind, times can't be negative. So if you want that, you have to
convert the numbers to text. Try this for your formula. See if you can
figure out how it works.

=IF(A1-B1<0,"-","")&ABS( ROUNDDOWN(24*(A1-B1),0) )&":"&TEXT( 60*ABS(24*(A1-
B1)-ROUNDDOWN(24*(A1-B1),0)), "00" )
Click to expand...

Thanks for that Jonathan, haven't a clue how that works.
 
Top