Hi OzGrid,

Although it may be little bit complicated on the topic title, it's something that I have been working on it for days. Excel can calculate negative time span values under certain circumstances if I am correct: 1904 system or converting to a text. At the beginning, I fully rejected to approach these particular solutions. Instead, I built up a formula to break the time into pieces by twos with LEFT, MID, RIGHT as if they are numbers separated with colon (like a modem's mac address) then applied a value. It worked! Subtracted one from another... Negative value appeared as I expected (I have to tell you that I also used a customer format 00\:00\:00.

However, it does not work when It comes to cells contain calculated span time values.

A2 contains this: 00:20:15 this comes from with INT formula and subtraction E2 and F2 both contains date with time.

A3 contains this: 00:10:15 this comes from another cell G2 (a span time; hours, minutes, seconds) plus TIME(0,3,30)

I want A4 to display subtraction A3-A2... Because neither A2 nor A3 is text. They are [hh]:mm:ss

I'd like to apply a trick... In A4, how can I convert them to text first, then apply a subtraction calculation and display the result as a value like they are numbers in the same formula without helper or VBA.

Your help will highly be appreciated on this mixed problem. Thanks in advance.

Although it may be little bit complicated on the topic title, it's something that I have been working on it for days. Excel can calculate negative time span values under certain circumstances if I am correct: 1904 system or converting to a text. At the beginning, I fully rejected to approach these particular solutions. Instead, I built up a formula to break the time into pieces by twos with LEFT, MID, RIGHT as if they are numbers separated with colon (like a modem's mac address) then applied a value. It worked! Subtracted one from another... Negative value appeared as I expected (I have to tell you that I also used a customer format 00\:00\:00.

However, it does not work when It comes to cells contain calculated span time values.

A2 contains this: 00:20:15 this comes from with INT formula and subtraction E2 and F2 both contains date with time.

A3 contains this: 00:10:15 this comes from another cell G2 (a span time; hours, minutes, seconds) plus TIME(0,3,30)

I want A4 to display subtraction A3-A2... Because neither A2 nor A3 is text. They are [hh]:mm:ss

I'd like to apply a trick... In A4, how can I convert them to text first, then apply a subtraction calculation and display the result as a value like they are numbers in the same formula without helper or VBA.

Your help will highly be appreciated on this mixed problem. Thanks in advance.

## Comment