title: Excel calculate the difference between times and highlight with conditional formatting
body: <p>I have a time sheet and I need to calculate the difference between two time values. As this sheet will be made available to all employees, I cannot make use of the 1904 date system hence I resolved to using this</p>
  
  <pre><code>=IF(A1-B1&lt;0, "-" &amp; TEXT(ABS(A1-B1),"hh:mm"), A1-B1)
  </code></pre>
  
  <p>This works and gives a negative sign to the negative values thereby overcoming the hashes ####### problem and display in Excel 2007 and higher. </p>
  
  <p>Yet I cannot create conditional formatting to the result as it is obvious that the values are interpreted as text and neither can I count a group of these values with a simple summation. </p>
  
  <p>Edit 1</p>
  
  <p><a href="https://i.stack.imgur.com/Zm1ur.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Zm1ur.jpg" alt="enter image description here"></a></p>