Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 at 7:12

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.