formula help please

[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

 

[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

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" )

 

[Andrew]

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" )

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

 

[Jonathan Rynd]

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

Take a look at it. Break it down piece by piece.