DIsplaying negative time values


N

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
 
Ad

Advertisements

F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Displaying a negative time 3
negative time values? 8
Negative Time 7
Time / Negative Time 2
Negative value 1
negative times. 1
Pivot Chart - negative values not displaying properly 2
Negative Time Calculation 3

Top