Excel Unplugged

Difference between two times formula in Excel

In short, times in Excel are decimal numbers between 0 and 1 that show you the percentage of the day that has past. Now with that in mind let’s calculate two differences between Arrivals and Departures. Most Excel users will use a simple Arrival – Departure formula. But things can go very wrong if you use that formula. Let’s look at two samples of time in Flight calculation.

So if you calculate the difference between 10:00 AM (Departure) and 6:00 PM (Arrival) with the =6:00 PM – 10:00 AM you are actually calculating the difference between 0.75 and 0.416666666666667 and you get 0.333333333 which is 1/3 of a day or 8 hours.

But here’s a problem. Let’s say that those times are 10:00 PM (Departure) and 3:00 AM (Arrival). For Excel those are 0.916666666666667 and 0.125. And if we follow our formula (=Arrival – Departure) we get -0.791666666666667 or -19 hours. Now that is wrong on so many levels. The first being the number itself, which should be 5 hours and second, Excel can’t really show negative times except if you use the 1904 Date System (you can read more about that here)…

Difference between two times

…but even then the -19 hours is just wrong.

The magic formula to use is

=MOD([Departure]-[Arrival],1)

Difference between two times

What the MOD function actually does is it leaves the positive times as they are (it calculates the difference between 0 and that result which therefor remains unaffected), but for negative times it calculates the difference between (-1) and the negative result given by the formula. As you can see this takes care of the negative results and even returns the correct amount of hours. But keep in mind that this will only show time differences up to 24 hours! But still it’s a great trick that takes us one step closer to eternal happiness.

Comments 7

  1. MF says:

    Nice trick! Before I read this, I used to add 1 to the arrival time if it is less than the departure time. Thanks for sharing. Merry Christmas!!

    1. Thank you and Marry Christmas and a Happy 2015 to you too!

  2. Anthony says:

    As the answer to this formula is a decimal part of a day, it is necessary to add *24 after the parenthesis to see how many hours elapse.

    1. You are right, but you could just as well adjust the formatting of those cells to h:mm… It depends on what you need.

  3. I think “=TEXT(H11-F11,”hh:mm”)” is also usefull and if we need only hours so “HOUR(H11-F11)” also ok.

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.