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)…

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

The magic formula to use is

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

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.