Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to calculate time differences within Excel but am having trouble with the formula.

I am comparing a screen time to the actual speaking clock time.

e.g

 Speaking clock = 13:00:00  
 System time = 13:05:00  
 Difference = 00:05:00  

The problem I encounter in Excel is when the calculation is reversed:

e.g

Speaking clock = 13:00:00  
System time = 14:55:00
Difference = -00:05:00

Excel refuses to calculate this time difference and just shows ######, it can't seem to handle the minus calculations for time. I'm sure it can, it's just me.

share|improve this question
2  
The difference between 13:00:00 and 14:55:00 is NOT 00:05:00 or -00:05:00 – Dave May 22 '14 at 7:12

Your question is unclear.

However, I think you want to find the difference between the 2 values even if one is negative. I don't think this can be done directly in Excel, as such, we need to work around it.

I will assume Speaking clock is in column A, and System time is in Column B. I've also used different values compared to your post.

=IF(B1-A1>0,TEXT(B1-A1,"h:mm:ss"),CONCATENATE("-",TEXT(A1-B1,"h:mm:ss")))

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .