I am trying to formulate a time card template so that once work times are entered regular hours and Overtime hours are separated. I have something like this.


This finds the difference. So if they work 9 hours it shows the difference of 1 which is the correct amount of over time worked. Now my problem is if there is no overtime worked I get an error in this cell. How do I fix it to show 0 or blank if the result is 0 or negative?


The easiest is to just wrap your formula in an IF() so you can control what the result is if your test above is <= 0 or not.

So take =B3-TIME(8,0,0) and switch to =IF(B3-TIME(8,0,0)<=0,0,B3-TIME(8,0,0))


You can also use an IFERROR function instead of an IF function if you get an Excel error.

That will handle all of the Excel errors you might get and will allow you to specify return values that won't make calculations (SUM, AVG, etc.) on the range return strange results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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