DIsplaying negative time values

[Neil]

Hi,

I have two columns of time values. I need to determine the difference
between the two values and return either a positive or negative time
value. The table below is in hours and minutes

Time A Time B
09:00:00 09:00:00
09:00:00 09:03:00
09:00:00 08:58:00

In the above example First row should be "00:00:00", Second row
should be "00:03:00", and Third row should be "-00:02:00"

When subtracting Time B from Time A for the first two rows i get the
correct value, but the third row returns as "##########" I have tried
changing do different formats but nothing seems to be working.

How do i go about displaying it with a negative symbol
Any help would be greatly appreciated.

Thanks
Neil

 

[Fred Smith]

To display negative times, you have two options:
1. Switch to the 1904 date system (beware of the pitfalls before you do).
2. Display the absolute value, and precede with a negative sign, as in:
="-"&text(abs(c1),"hh:mm:ss")
or:
="-"&text(abs(b1-a1),"hh:mm:ss")
or, putting them both together:
=if(b1-a1<0,"-","")&text(abs(b1-a1),"hh:mm:ss")

Regards,
Fred