Time differences in Excel

ABS to the rescue again

Here’s a technique to calculate the time differences when you aren’t sure which time is first or last. Note with standard Excel settings you cannot report negative time.

In the image below columns A and B have times.

Column C subtracts Time B from Time A. Column B subtracts column A from column B.

Note the cells that should display negative time have the train tracks error displayed. This display error means Excel can’t display the result.

Even though the above calculations display an error when done by themselves, you can convert the calculations into valid times using the ABS function.

See image below.

The formula in cell E2 is


This formula has been copied down the column. The ABS function removes negatives and leaves positive numbers unchanged.

Removing the negatives makes the time valid and so they display correctly.

The train tracks display errors in the top image are caused because the format applied is a time and time doesn’t allow negatives.

There is a setting change that allows negative time – see this blog post on its uses and disadvantages.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.