Showing the negative value of time. EX: 07:04:00 becomes -07:04:00

[SandsB]

Columns A and B show times. B-A could be positive or negative so I do this to get the difference: =IF(B2>A2,(B2-A2),(A2-B2)) This always gives me the difference in minutes because it always subtracts the larger number from the smaller one. You get ######### if you subtract a larger time from a smaller one. What I need though is to show the negative number if A is greater then B. How can I get the A2-B2 part of this formula to show -HH:MM:SS?

 

[SandsB]

And changing the format to -HH:MM:SS (with a - in front) changes the number to appear negative on the screen but when added to other numbers it's treated as a positive number.

 

[SandsB]

Can't b done?

 

[Alex Blakenburg]

Sadly you are correct and it can't be done.
All the options are pretty ugly.
The below link shows the 2 standard alternatives and then lots of people commenting with other things they have tried.
Displaying Negative Times

If you want to be able to do sum, average and other calculations on the difference you are probably best off just formatting the column as a number and either *24 changing the heading to hours or *24*60 changing the heading to minutes.